Skip to Content
0
Former Member
Jun 13, 2005 at 12:26 PM

Taking display value for calculations using structures in Bex query

64 Views

Hi

I am using Bex analyzer to do a simple report with two key figures ‘Sales’ and ‘Plans’ from cube and two more calculated key figures ‘Abs Deviation’ and ‘% error’. The report displays at category and product levels. Category is higher and one category contains multiple products.

The simple formulas for the two calculated KF are,

1. Abs Deviation = Abs (Sales – Plans).

Abs Deviation is set with the property Calculate result as ‘summation’ to add up Abs deviations at product level to show at Category level.

2. % Error = Abs Deviation / Plan.

The issue is with the % Error value at category level. At product level, all the values are showing correctly.

To illustrate the issue with a simple test case,

Categry Product Sale -Plan Abs Dev ----%Error

C1--


P1-- 100 -60 -- -40 --


67%

C1--


P2-- -50 120 -- -70 --


58%

-


C1 Total--


150 180 -- -110 --


17%

-


Observe that in the output the calculation for ‘%Error’ at product level is correct all the way. But the summary

calculation for ‘Error%’ i.e. C1 total which is showing as 17% is wrong. The correct value as per the formula should be 110/180 = 61%.

SAP, through OSS replied that it is not a bug but the default behavior of OLAP calculation. OLAP processor first calculates the formula for % Error and then shows the display value at C1 total for Abs Dev. Meaning, ‘% Error’ is calculated first and next the summation for Abs dev is displayed. Abs dev 110 at C1 level is only a display value but not the value taken for % Error at C1 level. Instead OLAP calculates the Abs Dev at C1 level by Total sale – Total plan = 180 – 150 = 30 and the % Error calculation takes this 30 value in the formula and gives % Error = 30/180 * 100 = 17% at C1 level. But 17% doesn't make any sense to the user. Further, to display value of 61% at % Error at C1, it is being advised that by defining two structures, I can force the calculation to take display value of 110 in the % Error calculation at C1 level by using the cell editor.

Has any body tried to achieve similar result as above by defining two structures and cell editor in the query? If so, can you please throw some pointers of how to define two structures in a query and how to force the % Error at category level to take the display value of 110 in the calculation? I have gone through the documentaion on structures and cell editor but the approach is not at all clear.

Thanks in advance for your suggestions.

Prasad

Unilever Asia IT department