Question:
Hi BW Gurus
We are using Bex Analyzer for our report presentation and facing serious issues with it's capability to calculate the result at the aggregate level. We extract APO data at product level and the product has got hierarchies of Category, Brand, Packsize.
For illustration, there are two key figures that are extracted into Cube Actual Sales, Plan. We created two calculated key figures on these basic key figures Absolute deviation, and % deviation.
Absolute deviation = Absolute difference between Plan and Actual sales.
% deviation = (Deviation / Plan ) * 100.
At Product level it displays correct. But at Category level, we want % to be calculated as (sum of deviation / sum of Plan) * 100. Instead, it calculates deviation at total level and calculates % for that value.
For e.g. in the cube the values are
Product----Actual sale-----Plan
P1---------------200----------400
P1---------------400----------200
After the report run with calculated key figures, it displays as
Product-------Actual sale------Plan-----------Abs.Deviation---% deviation
P1----------------200-----------400-----------------200---------------50%
P2----------------400-----------200-----------------200--------------100%.
-------------------------------------------------------------------------------
Category1-------600-----------600-----------------400---------------0%
--------------------------------------------------------------------------------
There is a problem for the sub-total for % deviation at category 1 level. As it can be seen from the above example, BEx Analyzer shows 0% for % deviation at Category 1 level which is not correct. The way it calculates is, at subtotal level, it re-calculates the absolute deviation = Abs(total plan - total actualsale) i.e. 600 - 600 which is 0. Subsequently, % deviation is also 0. But actually, it should calcuate the % deviation as total Absolute deviation / Total Plan * 100 which is ( 400 / 600 ) * 100. which is 66.66%. In stead of showing 66.66%, it is showing 0% which is making all the results at total and sub-totals are wrong.
We are interacting with SAP also on this issue, but so far no definite solution. Any bright ideas to handle this?
Thank you very much in advance for your valuable suggestions.
Cheers
Prasad
Answer:
Prasad,
I dont think SAP will give you solution for such type of problem.
XYZ.
Answer:
which version are you on?
which SP level?
Answer:
I have solution for your problem.
I have got the screen shots to solve the problem.
Give me your mailing address.
Answer:
Hi ssh,
can you send me solution?
Thanks in advance.
samuele.balzani@twinergy.it
_________________
Sorry for my english!!!!!
Answer:
Hi Ssh and others
Thanks for your update.
We are on bw 3.0b service pack 9.
My e-mail id: prasad.vaddadi@unilever.com
Thanks and regards
Prasad
Answer:
Hi all
Did a bit of trail and error on this problem by creating the absolute deviation as key figure in the cube itself rather than a calculated key figure. While populating the cube, I put a logic to calculate the absolute deviation. But this gives a very high %absolute deviation figure as it sums up product level quantities at the sub-total level at category.
Still looking for workable solution on this issue.
Dear Shh
Can you send me the screen shots please? We need to fix this problem to move on. Thanks for your suggestions.
Prasad.Vaddadi@unilever.com
Regards
Prasad
Answer:
Hi Prasad,
I am just trying to explain you in words. Still if you will face any problem please revert......then i will give you screen shots for the same. Okey.....
This problem is just because of aggregation. You are doing calculation of ABS Deviation after aggregation. If you will set the timeings of calculation before aggregation, your problem will be solve.
How to do it?? Hope you know the procedure for the same.
Just go to the ABS Deviation (this must be your calculated key fig), right click on it and select the property button.
In Properties of Calculated Key Fig window, click on Enhance , here you will find Aggregation time of calculation.
Choose before aggregation and press ok button.
Now save and run query. Because you have got the desired result.
SSH
Answer:
Dear Ssh
Thanks for your solution. I have tried the option and it works fine at Product level. But while navigating, if we display the report only at brand or category level with out selecting Product, the problem is back. Please see the following scenario.
Cube:
Product---- Actual sale-----Plan
P1---------------200----------400
P2---------------400----------200
P3---------------100----------300
P4---------------600----------450
Product heirarchy:
Product-----Category--------Brand
P1-------------C1---------------B1
P2-------------C1---------------B1
P3-------------C2---------------B1
P4-------------C2---------------B1
Display of report with ‘Before aggregation’ selected as suggested:
Brand---Cat----Product----Actual-----Plan---Abs.Dev---%Abs Deviation
B1------- C1-------P1---------200-------400------200-------------50%
B1------- C1-------P2---------400-------200------200-------------100%
---------------------------------------------------------------------------------
---------------C1 Sub-tot------600------ 600------400------------ 66.67%
---------------------------------------------------------------------------------
B1------- C2-------P3---------100-------300------200-------------66.67%
B1------- C2-------P4---------600-------450------150-------------33.33%
---------------------------------------------------------------------------------
---------------C2 Sub-tot------700------ 750------350------------ 46.67%
---------------------------------------------------------------------------------
The above display is exactly what we would like to see, but if we display the report by suppressing product code
Brand---Cat------Actual-----Plan---Abs.Dev---%Abs Deviation
B1-------C1 ---------600 ----- 600------400------------0%
B2-------C2 ---------700------ 750------350--------- 6.67%
In the above C1 cat calculation, observe that % absolute deviation is again taken as (600-600)/400 = 0% and for C2 it has taken as (750-700) / 750 = 6.67% both of which are again wrong. The correct % abs deviation for C1 = (400/600) * 100 = 66.67% and for C2 (350/750)*100 = 46.67%.
How do we get around this problem?
Thanks and regards
Prasad
Answer:
Hi Prasad,
I have tried the same. It is working perfectly. I dont know why you are getting this error.
Give me more details about the properties of Calculated Key Figs ( ABS Deviation and % Devaition).
Regards,
SSH.
Answer:
Hi Prasad,
I have tried the same. It is working perfectly. I dont know why you are getting this error.
Give me more details about the properties of Calculated Key Figs ( ABS Deviation and % Devaition).
Regards,
SSH.
Answer:
Just something to check:
Is your %Deviation calculation defined as (Plan-actual)/plan *100
OR
Deviation/plan * 100
It should be deviation/plan *100 or it will calculate incorrectly at total line
Answer:
Dear Ssh and avanie
Thank you very much for your suggestions. I used the before aggregation option for both Absolute deviation and % absolute deviation and used absolute deviation / plan for in calculation for % absolute deviation. It works fine. I checked for the first level heirarchy and it works. I am further navigating by supressing the other characteristics to confirm the result. I can see that it should work.
Many many thanks for all the enthusiastic BW friends
Thanks and regards
Prasad
Answer:
hi ssh,
can i get also the screen shot for this problem?
thepalm00@gmail.com
Thank you
thepalm00