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 *** KeyF Y *** 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
you can try to use cell formulas in your query designer. In your cell formula you can take data directly from the result in a specific cell. Try to see the following document:
Hope it helps.
Please also check this below PDF for cell restriction in query designer.
Hope it helps.
The problem is that you calculate the cumulative KFs directly in your query and it is not possible to use them with a formula without using cells: you read data, cumulate on the query view and calculate the formula at the same time.
You can try to use two precalculated key figures: maybe they would calculate the result record by record according to the dirll-down directly when you read data from the InfoProvider.
If this solution works fine, you can simply use a formula on these two precalculated KFs.
Hope it helps.
Thanks for your input Simone!
I do understand that the problem is because of the sequence in which the calculations and the cumulated property are applied.
What do you mean with a precalculated key figure?
Do you mean to calculate the cumulated key figure in the InfoProvider? This isn't realistic either as the date range in the rows is a variable input parameter of which the values for the cumulated key figure are dependent (number of possible date ranges = 365 days * 365 days * nr of years ).
The calculated KF, as I know (but I can say a mistake), is based on the data in the InfoProvider and it is not calculated in InfoProvider level but only when you open a query on that InfoProvider.
So, if you have days from 2009 in your InfoProvider and your input period is 01.01.1900 - 20.09.2011, the query calculate the KF only for data in your InfoProvider and not from the input range. Also the calculation is the same of the "classic" cumulate key figure and maybe you can improve your performance because you calculate those KFs when you read data and not after reading data.
Again, the logic is the same of every cumulated KF and this precalculated key figure is not scored in the InfoProvider. Please refer to the following link:
So, only for trying, you can use my suggestion: maybe it will not work anyway but I hope that my suggestion can help you.
Go to your Calculated Keyfigure and double click on it ,there you can see Calculation Tab. In that tab you can find Calculate single Values : Normalize according to Overall Result and then tick the check box Cumulative along the Rows. Now you may get the % cumulation. if you dont get try the other option Normalize according to Next Group Level Result.
Here what it says those settings are first it will give the percentage according to the group level result display(if you make the chraceteristic as Always Display) and then start cumulating your percentage without any break. Do let me if you get it,