Can I get a new CKF from the calculated results of the other two KFs?

[Former Member]

Hi gurus,

I uses some boolean operators to calculate two key figures, say KF1 and KF2. What I want to do next is to calculate KF3 = sum(KF1)/sum(KF2) . The trouble is: sumct function will get a different result from the real summation of KF1 or KF2. I can only get it by means of the calculated result. However this kind of result is just for display, isn't it?

Then, I can't find anyway to calculate KF3!

Help!

Regards

[Former Member]

Hi,

Also please check the your computer Regional settings for excel this may round the value. This you can check after checking the deceimal settings in Query designer for that CKF.

Regards,

Vishwa.

[Former Member]

Hi guys,

I am quite sure it is not the problem of decimal settings.

[Former Member]

I found that no matter what kind of boolean operation I put on the key figure, it wil become useless in the result. That's why I alway get the rPrice value of 12, the same as the Price value.

Did anyone get into this trouble before?

Regards.

[Former Member]

Calculation is going fine according to the values in each and every record.

Like 600/50 = 12 which is true in case of values in result.

But to meet your requirement you should not allow the result to be 600/50.

Instead here is a way you can get it as 500/40 = 12.5

Have two more extra calculated keyfigures ( can he marked as hidden )

Rev Qty (Rev>100)Rev (Rev>100)Qty Price rprice

Jan. 100 10 0 0 10 0

Feb. 200 20 200 20 10 10

Mar. 300 20 300 20 15 15

Res : 600 50 500 40 12 12.5

Here Price should be defined as Revenue/Quanity

But rprice should be defined as (Rev>100)Revenue/Rev>100)Quantity

Please consider the case 0/0. You can use NDIV0(X) function

which will result any value divided by 0 to be 0.

[Former Member]

Hi Venkata Naresh Adabala

Thanks a lot for your kindly answer. You really understood my problem. I've already tried your way, but I still got the following result.

Rev Qty (Rev>100)Rev (Rev>100)Qty Price rprice

Jan. 100 10 0 0 10 0

Feb. 200 20 200 20 10 10

Mar. 300 20 300 20 15 15

Res : 600 50 600* 50* 12 12*

It seems the boolean logic doesn't work in the result.

Regards

[Former Member]

For the added new keyfigures, (rev>100)rev and ( rev>100)qty

please change the properties.

In the calculation tab, make the "Calculate result as" to "Total"

So result of (rev>100)*rev will show 500 instead of 600

and result of (rev>100)*qty willl show 40 instead of 50

and finally rprice will show 12.5 instead of 12.

Is this solution solving ur prob? if so do needful, give needful.

[Former Member]

Hi Venkata Naresh Adabala,

I tried...the result of (rev>100)rev is indeed 500 instead of 600, and result of (rev>100)qty is 40 instead of 50, but rprice is still 12. Using calculated result as total is only for display.

[Former Member]

i was not there with system presently.i checked ur post just now. Tomm i will try to find someother means of calculating this when im with the system.

One way wud be,

if calculations happen after the row (with rev<100) is removed, then everything will be perfect.

Cant we put a condition to remove rows with rev<100 ?? Is it necessary to show the row with rev < <=100 also in ur report??

[Former Member]

Hi, Y.Sun,

Pls check the KF property, calculate result as: summation. It will work for you!

Hope it helps,

Sam

[Former Member]

It doesn't work even after I put a condition to remove those rows. I am thinking...maybe it's because when it comes to the calculation, the result line will skip over the condition or boolean operator?

>

> One way wud be,
> if calculations happen after the row (with rev<100) is removed, then everything will be perfect.
> Cant we put a condition to remove rows with rev<100 ??  Is it necessary to show the row with rev < <=100 also in ur report??

[Former Member]

Hi

Here I can't use the 'calculate result as: summation' or even 'calculate result as: average' for the rPrice property, because what I expect is: rPrice = summation((rev>100)rev) / summation((rev>100)qty)，but until now the result line of rPrice in my query always gave me rPrice = summation(rev) / summation(qty)，

Thanks!

>

> Hi, Y.Sun,
> 
> Pls check the KF property, calculate result as: summation. It will work for you!
> 
> Hope it helps,
> 
> Sam

[Former Member]

One solution i can think of is,

Create one charecterstic in the cube, PASSED which shud be filled in using update rules. If rev>100 then fill PASSED = 'Y' else give PASSED='N'.

In the query , you can filter the charecterstic PASSED='Y'.

This will remove the row with rev 100 in your example and it will not be considered in teh calculations..

But, i dont think this is a good solution.After all, for a reporting requirement we are adding new chare to cube. If this is ok for you, then you can follow this.

[Former Member]

There should not be any problem. Go ahead and create the KF3 based on KF1 and KF2.

Thanks...

Shambhu

[Former Member]

My problem might be much clearer from this example.

Revenue Quntitiy Price rPrice

Jan. 100 10 10 0

Feb. 200 20 10 10

Mar. 300 20 15 15

Result 600 50 12 12*

Price = Revenue/Quantity

rPrice = (Revnue>100)*(Revenue/Quantity)

From my query I can only get the value of rPrice: 12 not (200300)/(2020)=12.5

[P095090]

Can you please check the decimal setting for you Key figure here

[Former Member]

Hello,

your question is not very clear.Bt yes u can definitly create a calculated key figure using other two calculated key fig.