Sales of Last two days of each week in a given Posting Period ???

[Former Member]

Hi,

Here I would like to share one of my tech. requirement in Bex query, your inputs are much appreciated -

Scenario - In report I want to show "Sales for Last two days of week". I elaborate more like -

(1) User inputs will be given for "Fiscal period" through Variable.

(2) In Row all weeks lying in this period will be displayed.

(3) In Column 1, Sale for each week will be shown.

(4) In Column 2, Sale for Last two days of each week is to be displayed. (here we need to restrict Sales with last 2 Calendar days of each week.)

Point 4 is of my concern. Any suggestions will help a lot. Points must be assigned.

Regards,

Saurabh

[Former Member · Accepted solution]

Hi,

I do not have a very good solution for you - see if this is of use.

Firstly, I am not sure if it is possible to show the query like you want, with the weekly sales and the "last 2 day/week" sales combined and intertwined in one query.

To create a query which will only show the sales of last 2 days of every week, what you can do is to create a variable on 0calday which will be 'multiple single values' type and will be calculated based on the user input of fiscal period (exit variable). This exit logic should assign the variable the last 2 days of each week falling within this period.

Now you can put 'week' in the in the rows and restrict the query filter using the 0calday variable; put the KF in the columns area. This would lead to one row being displayed for each week and sales for only the last 2 days showing in that row.

Creating a query for weekly sales is simple - maybe then you can put these two queries side by side in a workbook/web template.

[Former Member · Accepted solution]

Solution 1: If you can add a characteristic to the Cube, you can add a char for "Weekday" (value will be between 1 - 7). Restrict the key figure in column 4 based on this char for values 6 and 7 (last 2 days of week).

Solution 2: If you cannot reload data in cube, you can create a virtual characteristic for "Weekday" and populate it using exit and restrict it in same way as solution 1.

Regards,

Sree