Result Row Calculation using other values in Result row

[Former Member]

Hi

I am busy writing a query in Bex and have come upon a problem. I start with 3 key figures - A, B and C. I then have 2 calculated key figures Z and Y.

CKFZ = KFA * KFB. (Result Calculation is set as total)

CKFY = KFC / CKFZ

The result set will look something like this:

CalMonth KFA KFB KFC CKFZ CKFY

01.2008 10000 0.80 6000 8000 0.75

02.2008 16000 0.75 4000 12000 0.33

Result 26000 1.55 10000 20000 0.08

What I want to achieve is for CKF Y result to be 0.50 (KF C Result / CKF Z). What the result is doing is KF C Result / (KF A Result * KF B Result).

Any help or ideas will be greatly appreciated.

Vic

Edited by: Victor Elms on Sep 9, 2008 6:07 PM

[Former Member]

Hi All

Thanx for all the responses. Unfortunately, none of the suggestions have had the desired effect.

When I use the exception aggregation, the result is either 1.08 or 0.25, depending on which characteristic I am using.

When I use the first value, the result is 0.75.

And, as there are no collisions, the "Result from this formula"/ "Result from competing formula" does not appear on the formula's properties.

Whichever way I am trying, the formula either wants to add up the results of individual lines or break the formula down to it's lowest components, i.e. either 0.75 + 0.33 or (6000 + 4000) / ((10000 + 16000) * (0.80 + 0.75)) which results in either 1.08 or 0.25 (I noticed I made an error on the example I gave).

The result that I require would be (6000 + 4000) / (8000 + 12000) giving me 0.50.

[Former Member]

In BI7.0, it's not collision of formula, but you've to find in the "calculation" properties of the KF a "Calculate along the row" ... This should solve your problem.

[Former Member]

Hi,

In the CKF properties you will find option of formula collision -> "Result from this formula"/ "Result from competing formula" , and it should work as per expectation .

Hope that helps.

Regards

Mr Kapadia

[Former Member]

Hi,

Create a formula which will look like SUMCT(KFC) / SUMCT(CKFZ) and in the calculations tab you can keep Calculate result as First Value.

Regards.

[Former Member]

define a calculated key figure similar to the BW 3.5 calculated key figure, but instead of specifying the time of calculation to be before aggregation, specify that you want a total with reference to a characteristic and then specify after aggregation.

[Former Member]

We are on 2004S / BI7

I have tried to use aggregation, but the result then comes out to 1.08.

[Former Member]

for CKF Y you need to do exception aggregation to calculate individual results and add them up.

what version r u on?

Edited by: Raj Coppar on Sep 9, 2008 12:19 PM