This website is not affiliated with, sponsored by, or approved by SAP AG.

How to calculate a cumulative percentage in BEx Query Desig?

Business Warehouse

Moderators: Snowy, thx4allthefish

How to calculate a cumulative percentage in BEx Query Desig?

Postby PascalV on Mon Sep 19, 2011 6:39 am

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

PascalV
 
Posts: 1
Joined: Mon Sep 19, 2011 6:35 am

Return to Business Warehouse

Who is online

Users browsing this forum: Yahoo [Bot] and 4 guests



This website is not affiliated with, sponsored by, or approved by SAP AG.