Question:
Hi all,
I have a case where I want to calculate KF in different levels.
Sales Office / Mat Group / Material / KF Value
A ................ 1 .............. q ............ -10
A ................ 1 .............. x ............ -25
A ................ 2 .............. y ............ 30
A ................ 2 .............. z ............ -60
I want to analyse this KF Value in different ways:
Absolute Value on Sales Office Level : this is simple with a extra KF in the Cube and where for every row the absolute value is calculated : 10 + 25 + 30 + 60 = 125
Absolute Value on Sales Office/Mat Group Level: I want to do the same calculation within the Material Group characteristics : first sum up -10 and -25 and then take the abs value (35), then sum up 30 and - 60 and take abs value (30) which gives a total of 75.
Is there a way to achieve this??? I can't figure one out since in Bex the claculatins are done for every record of the Cube
Any help appreciated!!!
TFr
Answer:
For the first example, it's quite simple, create a calculated key figure equal to ABS using before aggregation.
There is an error in your sentence 35+30 = 65, and not 75 (pentium bug ? )
For the second one, use the normal KF, create a calc. KF in the Key Figure area (a after aggregation KF), which retrieves the Absolute value of the Key Figure, and assign the Result Value in the Properties of the KF as Summation.
Assuming that the cube is as you defined, you should see this :
Sales Office / Mat Group / KF / KF Before agg / KF After agg
A...................1..............-35.......35...............35
A...................2..............-30.......90...............30
A..................Result........-65.......125.............65
Remark : if y is -30 instead of 30, and z 60 instead of -60,
Result Line is
A..................Result.........-5.......125.............65
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
Fred_gauthier,
tnx for the reply, but your solution for my first example won't work. If you work with a CKF in BEx for calculating the ABS you will get this:
Sales Office / Mat Group / Material / KF Value / CKF As ABS
A ................ 1 .............. q ............ -10 ............. 10
A ................ 1 .............. x ............ -25 ............. 25
A ................ 2 .............. y ............ 30 .............. 30
A ................ 2 .............. z ............ -60 ............... 60
Total...............................................-65................65
This is because he also 'calculates' the CKF horizontally for the Total line, instead of summing it up vertically.
Maybe there is a way to force the sum vertically so you would get 125??? If you know this, please tell me...
Answer:
No. Please define your calculated KF, in the properties,
the "Calculate Result As" as Summation, and not as "(Nothing designed)".
If you don't do this, I agree that BW Applies first aggregation on the Key Figures, and then the formula.
If you design the Result as "Summation", BW will add the detail values instead of doing what I previously described.
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.