How can a cumulative percentage be calculated in BEx Query Designer?

[Former Member]

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 cum

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 cum vs KeyF X cum

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

[Former Member]

Hi I am facing the same issue - did anyone manage to get this issue resolved ?

[Former Member]

Hi Dolly,

No, unfortunately! I already tried with those settings as well (tried all possible options and combinations). But unfortunately without getting the expected result

[Former Member]

In other words:

calculated key figures on the InfoProvider which can be reused in other queries

vs

local formulas within this query.

I did try both options. But they both gave the same problem unfortunately.

[Former Member]

Hi Pascal,

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,

Regards,

Dolly

[Former Member]

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 ).

[Former Member]

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:

http://help.sap.com/saphelp_nw70ehp2/helpdata/en/13/e072abaddb574284d22361f0b824bf/content.htm

So, only for trying, you can use my suggestion: maybe it will not work anyway but I hope that my suggestion can help you.

Best regards.

Simone.

[Former Member]

Thanks for the input!

But the 'define cells' property seems to give the same issue.

Moreover the end user would loose all drill down capabilities like switching from day to month, adding other free characteristics to the result, ...

[Former Member]

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.

Best regards.

Simone.

[anshu_lilhori]

Hi,

Please also check this below PDF for cell restriction in query designer.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/40537fbd-2194-2e10-50a0-d4b01b143...

Hope it helps.

Regards,

AL

[Former Member]

Hi Pascal,

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:

http://help.sap.com/saphelp_nw04/helpdata/en/cb/89fa3a0376a51fe10000000a114084/content.htm

Hope it helps.

Best regards.

Simone.