Restrict sales by dates from custom characterisics

277 Views Last edit May 17, 2021 at 02:49 PM 3 rev

Hi gurus,

I have the following task:

In one report I need to show three columns with sales:

1. Sales during selected promotion (I have this, it is ok)

2. Sales of the same promotional products, but for period of 21 days before the start of the promotion

3. Same logic as in 2, but 21 days after the end

In the filter, the users should select a promotion (we are assuming they don't know the exact start and end dates of promotions).

I created two date InfoObjects - ZDAYSBFPR and ZDAYSAFPR and added them as nav attributes to 0RT_PROMO (I use this object in the cubes and CPs). I used add_to_date formula in the transformation to fill the fields with the exact data.

The problem is when I try to filter the sales. In theory they should be:

Sales 2 restricted by dates ZDAYSBFPR (>=) and STARTPROMO (<)

Sales 3 restricted by dates ENDPROMO (>) and ZDAYSAFPR(<=)

EDIT:

I figured out the reason for the error below - replacement path variables could not be used in restricted key figures.

Still I don't know how to solve the problem.

**********************************************************************************************************************

I tried setting Replacement path variables for each of the four characteristics where I used queries. Each query has the same filer for promotion with user input and returns the needed date. I created restricted key figures using these variables, but when I put them in the report query I get error

Cannot use variable ZVAR_DAYS_BF_PROMO in structure elements

Message no. BRAIN103

Diagnosis

Variable ZVAR_DAYS_BF_PROMO is used in a structure in the query. This is not allowed.

Incorrect FRANGE row in FORM/DIM/FAC for InfoObject 0RT_PROMO__0RT_PRSLFRM

Message no. BRAIN415

Diagnosis

This is a generic error message that refers to incorrect content in the definition of the query.

The report is based on a composite provider and the system is BW 7.5 on Hana.

*****************************************************************************************************