Apply rule to all consolidated parent elements

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

    • Apply rule to all consolidated parent elements

      Hi everyone,

      I'm stuck with PALO's handling of consilidated elements. I have two cubes:

      1) Balance
      Dimension MONTH with consolidated YTD elements, e.g. Mar YTD = {Jan,Feb,Mar}
      Dimension CLIENT with

      Source Code

      1. Group
      2. '- Subsidiaries
      3. '- CAN_Subsidiary (consolidated 0.5)

      2) FX
      Dimension CURRENCY with elements EUR, CAN, CAN (average)
      Dimension MONTH_FX, same as MONTH but Jan YTD, Feb YTD, Mar YTD... are basic elements

      I use MONTH_FX because the YTD exchange rates are different than the average of the monthly exchange rates.

      Now I have a rule that converts the values of the subsidiary into EUR:
      ['CAN_Subsidiary', 'EUR'] = ['CAN_Subsidiary', 'CAN'] * PALO.DATA("DB","FX","CAN (average)",!'MONTHS')

      Let me give a numeric example:

      exchange rates: Jan = 1; Feb = 1; Mar = 1; Mar YTD = 2
      balance position: Jan = Feb = Mar = 100; Mar YTD = 300

      Supposed outcome in EUR:

      Source Code

      1. Jan | Feb | Mar | Mar YTD
      2. 100 | 100 | 100 | 600
      This is what PALO gives me when I choose YTD view and as client CAN_Subsidiary.

      But if I choose as client Subsidiaries the outcome is:

      Source Code

      1. Jan | Feb | Mar | Mar YTD
      2. 50 | 50 | 50 | 150

      But it should be

      Source Code

      1. Jan | Feb | Mar | Mar YTD
      2. 50 | 50 | 50 | 300

      Is there a way to get the desired outcome without writing a rule for each consolidated element?

      I know this case may be not clear at first, so if you have any question, please ask. Thanks for your help!
    • Hi, just don't specify the subsidiaries in the rule. With your approach you would need to write a rule for every single subsidiary - if you have more than one.
      The following rule gives you the desired output. Of course you need to check it in a more real world model.

      Source Code

      1. ['EUR'] = ['CAN'] * PALO.DATA("Demo","FX","CAN (average)", !'Month')

      I assumed that Jan, Feb, Mar are also base elements in your month_fx dimension.
    • Thanks for your quick reply. Your suggestions about the Jan, Feb, Mar being base elements in MONTH_FX is right.

      I recognized that I forgot to mention that there are other units grouped under "Group" which report in EUR. In other words my data basis consists of values in EUR and values in CAN. So I can't use your suggestion.

      My (still condensed) CLIENT dimension looks as follows (reporting currency in brackets):

      Source Code

      1. Group
      2. '- Europe Subsidiaries
      3. '- FRA_Subsidiary (EUR)
      4. '- ITA_Subsidiary (EUR)
      5. '- Non-Europe Subsidiaries
      6. '- CAN_Subsidiary (consolidated 0.5) (CAN)
      7. '- USA_Subsidiary (consolidated 0.5) (CAN)

      Hmm, it seems that there is no other way to get this done than writing a rule for each consolidation element. Any other suggestions?