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!
      Thanks in advance,

    • 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...

      • Screen001.jpg

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

        72.55 kB, 809×300, viewed 373 times
    • 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!

      • Screen001.jpg

        45.96 kB, 483×419, viewed 406 times