% Calculations of Cummulated Values

[former_member314446]

Hello,

I have a query that is using two key figures which are cummulated down the column (Plan and Actual). I have one formula which subtracts the Plan from Actual and again is cummulated. Finally i have a Variance formula which takes the previous formula divides by actual.

The result of that is looking the values before they are cummulated instead of after. I have read the OLAP engine does not support this.

Does anyone have any workarounds, i have seen some other posts on here but none with specific examples of how to do it.

We are using 04s, BI sp 11, and BEx sp 12.

Thanks in advance.

[Former Member]

Did you try using Cell definition hope it helps.....

Create a column for plan amount and Actual amount.

Just create a empty columne and name it as Subtraction (Put anything in it , it is basically created so that you can use the Empty cells and then use Cell definition)

Repeat the same and create a Column, name it as Variance and Put anything in it.

Now click Cell definition.

Create Cell reference for Plan amount and Actual amount as Cell 1 and Cell 2

Now in the cell box of Difference right click and select new formulae

Edit it and Subtract Cell 1- Cell 2

In Variance cell box , hit new formulae and use Cell 1 and 2 for creating required formulae.

Hope it helps.

[former_member314446]

Do I need to set up two structures in order to use this functionality of cell definition? This is all done through query designer, correct?

[former_member314446]

Ok I set up the cell definition and cell reference explained above but the calculations are still using the value before cumulation.

Actual (non-cum) Actual (cum) Variance (Actual/x) Variance (wanted)

40 40 40/x 40/x

40 80 40/y 80/y

0 80 0/z 80/z

0 80 0/x 80/x

I know that a macro could be written in excel but we need this report to open through the portal not query analyzer.

[Former Member]

Hello T_Mac,

I have got the same problem. Using BW 3.5. Could you please tell me how you handled it? If you wrote a macro, can you post it?

Or do you have another workaround.

Hope to hear from you!

kind regards HARRY

[Former Member]

Hello,

meanwhile I have found a solution / workaround:

You can have a look at thread "Do further calculations with results of a column with cumulated values".

Mybe that helps...

best regards Harry