How can a cumulative percentage be calculated in BEx Query Designer?
I don't manage to get BEx to calculate a cumulative percentage.
A lot of threads already exist on this topic, though none of them provides a good solution to this very basic report requirement!
The query looks now as:
KeyF X KeyF Y Y %A X KeyF X cum KeyF Y cum Y %A X ***
Day1 2 1 50,00 2 1 50,00
Day2 4 1 25,00 6 5 25,00
Day3 3 2 66,67 9 8 66,67
Day4 2 2 100,00 11 10 100,00
Day5 5 2 40,00 16 15 40,00
Logic for each key figure:
- KeyF X: number of records
- KeyF Y: restricted key figure with KeyF X and an extra restriction
- Y %A X: percentage share of Y vs X
- KeyF X cumulated: calculated key figure with KeyF X and additional property 'cumulated' in the calculations tab
- KeyF Y cumulated: calculated key figure with KeyF Y and additional property 'cumulated' in the calculations tab
- Y %A X cumulated: percentage share of KeyF Y *** vs KeyF X ***
The last key figure (Y %A X cumulated) is calculated the wrong way as it doesn't take the cumulated values, but the not-cumulated values to make the calculation.
The values should be: 1/2 = 50; 5/6 = 83,33; 8/9 = 88,89; 10/11 = 90,91; 15/16 = 93,75.
I know the underlying reason has to do with the fact that the property 'cumulated' is only applied after the OLAP engine executed the calculations, which makes that key figure "Y %A X cumulated" is calculated with the non-cumulative values instead of the cumulated values.
But how I can make this work? I would be surprised to see that this reporting tool can't fulfill this fairly simple reporting need!!
I have to use the Web Analyzer which makes that working via workbooks in BEx analyzer is not an option!
I already searched intensively on SDN and SAP notes and played with all possible settings (properties in all tab pages of the calculated key figure, calculated key figures vs local formulas, ...).
Edited by: PascalV on Sep 19, 2011 5:13 PM