Question:
We have a multicube taking data from two cubes: demands and forecasts. We have a query where we are calculating the deviation, which is the difference between demands and forecasts, in absolute values. Example:
Demands Forecast Deviation
100 80 20
100 94 6
100 133 33
So, Deviation is a formula: ABS(Demands - Forecast)
Now, the problem is in the results. We want the totals for Deviation should be the sum of the deviation. In the example, 59.
Demands Forecast Deviation
100 80 20
100 94 6
100 133 33
Results 300 307 59
However, instead of adding the formulae for the single values, the query is applying the formula to the total values. So we get:
Demands Forecast Deviation
100 80 20
100 94 6
100 133 33
Results 300 307 7
What can we do to get the results we want? I tried to create a calulcated key figure and set it to "Before aggregation", but it does not allow me, because the data is being taken from two different InfoCubes.
Any idea?
_________________
Leandro,
Tuly Idiots Shadow Supporter
Answer:
Unfortunately this is working exactly how SAP designed it to. You can get the correct results by simply telling the difference to calculate the result as summation. But the problem is if you are then using the difference in another calculation. If this is the case, you can either use a Virtual Key Figure to calculate the difference, change the results with VBA or with the Web Table API.
Thanks,
jlaruss
Answer:
Thanks jlaruss. You're right: summation works, but only for display, and I am using the deviation in other calculations, so it doesn't really help me. I know I can get it with VBA, or even merging all data into one Cube, but I want to avoid those. As to a Virtual Key Figure, I will give it a thought, but I'm not sure...
_________________
Leandro,
Tuly Idiots Shadow Supporter