# How to consolidate "C" calculated elements?

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # How to consolidate "C" calculated elements?

Hi experts,

I need an advice on an apparently simple task (Sales planning).

I have some "C" rules by which I calculate some values; I need to keep them at C level, not N (base level) because of performance issues.
At a certain point, I have to consolidate (Sum) these calculated values on the "father" of those C elements (I know that for this Customer I have to consolidate instead of calculating using the "IsPlanningLevel" attribute): how could I write the rule to get this result?

My calculation rule:

### Source Code

1. ... = C:IF(PALO.DATA("CPM","#_Customer","IsPlanningLevel",!'Customer') = "Y",['Average Price'] * ['Quantity'],CONTINUE())

Needed rule: rule for consolidating those calculated values such as:

### Source Code

1. ... = C:IF(PALO.DATA("CPM","#_Customer","IsPlanningLevel",!'Customer') = "N", ?how do I say here "sum the calculated values for element children"?)

I was expecting Palo to sum up the calculated values naturally but it is not...

Your help will be exceptionally useful!

Realquo
• if I understood your setup correctly this one should do it:

### Source Code

1. ....= C: IF(PALO.DATA("CPM", "#_Customer", "IsPlanningLevel", !'Customer') == "Y", ['Average Price'] * ['Quantity'], STET())

Thus you only need to consider to flag your "Y" customers. IsPlanningLevel = N and Empty cases follow the dimension aggregation.
• Hi kratzer,

thanks a lot for your suggestion; unfortunately I was unable to make it work, even if it seems logical to me (on the other side, my first rule should work the same way):

- you can see from the screen 1 the configured rule on the first customer (I just manually put "IsPlanningLevel" = "Y" on it), and the amount is calculated properly;
- at parent level (the green cell) the STET() command causes "No rule for this cell" as expected, but the consolidation does not happen...

Any idea? Thanks in advance...

RQ
Images
• Screen001.jpg

80.3 kB, 957×379, viewed 408 times
• Screen002.jpg

72.55 kB, 809×300, viewed 375 times
• Hi,

why don't you run your calculation on B: Level? Then the roll-up will work.
That looks like a bug to me.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• thanks for the images. You could try to reproduce the issue with the biker model or some simple model where all other business logic is omitted. I suppose the problem is model-inherent.
• Hi,
thanks all for you replies.

@tish: no, I cannot calculate at B level for performance reasons; moreover I need the user to enter some string data at C level, that are then converted in values into the rule.

@kratzer: I reproduced the behavior in the "Demo" database, "Sales" cube. You can try with minimal setup:
- create a new Measure named "Test", numeric type
- enter such a rule:

### Source Code

1. ['Test'] = C:IF((!'Products') == "Monitors",20,CONTINUE())

You'll end up in a similar situation (please see attached).
Obviously in this simple case I could write ['Test','Monitors'] = C:20 (since I can use the element name to define the slice to which I apply the calculation) , and the aggregation would work, but in my real model using an attribute would be more practical in order to avoid to hard-code element names into the rules.

So my original question comes back: is there a way to define such a rule: .... : C: IF( ......, SUM(Below), ...)?

Your support is greatly appreciated, thanks!

RQ
Images
• Screen001.jpg

45.96 kB, 483×419, viewed 407 times