Question:
Hi all,
I have an issue regarding the calculation of average sales of the last three weeks. So, the average sales is:
week 3 --> (week 1 + week 2 + week 3) / 3
week 4 --> (week 2 + week 3 + week 4) / 3
week 5 --> (week 3 + week 4 + week 5) / 3
week 6 --> (week 4 + week 5 + week 6) / 3
(etc.)
So, the report needs to look like this:
week sales average sales
1 10
2 15
3 10 11,7 = (10 + 15 + 10) / 3
4 20 15 = (15 + 10 + 20) / 3
5 25 18,3 = (10 + 20 + 25) / 3
6 30 25 = (20 + 25 + 30) / 3
(...)
A requirement is also that all the weeks need to be displayed. This rules out the possibility to start with a pop-up, where the user enters a week and then use a restricted key-figure with an off-set.
I have also considered to make restricted keyfigures for each week, as also suggested in /forums/viewtopic.php?t=84733. However, the problem with this, is that a year does not have a fixed number of weeks. This can be either 52 or 53.
Answer:
The alternative that I came up with, is to create a start routine at the update rules.
Input for update rules:
Week no. Document number Amount
48.2004 90021822 E 5.000
Output to the infocube:
Week no. offset Document number Amount
48.2004 0 90021822 E 5.000
49.2004 -1 90021822 E 5.000
50.2004 -2 90021822 E 5.000
And then, I can create a query as follow:
Transaction data in cube for week 007.2006
Week no. Amount Week Offset Amount
48.2004 E 5.000 --> 6.2005 -11 E 5.000
50.2004 E 2.000 --> 6.2005 -9 E 2.000
32.2005 E 6.000 --> 6.2005 -3 E 6.000
48.2004 E 5.000 --> 7.2005 -12 E 5.000
50.2004 E 2.000 --> 7.2005 -10 E 2.000
02.2005 E 6.000 --> 7.2005 -4 E 6.000
02.2005 E 6.500 --> 7.2005 0 E 6.500
However, this requires extensive ABAP in the update rules and explodes the number of records in the infocube. Especially, because in my case the number of average weeks is not 3, but 13. So, in that case the number of records grows with a factor 13.
Answer:
I suggest using the cell definitions for this query. I have done this many times and it works fine. Here is more information on cell definitions:
http://help.sap.com/saphelp_bw33/helpdata/en/cb/89fa3a0376a51fe10000000a114084/content.htm.
Thanks,
jlaruss