BoP EoP calculation with Rules for each year seperately

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

    • BoP EoP calculation with Rules for each year seperately

      Hi there,

      this is a general question and a cry for help at the same time :D .
      In the scenario, I have a calendar dimension (basically the standard jedox-Calendar with months and weeks in parallel with dummy elements for every month) and a movement dimension (BoP, NetMove, EoP).

      Currently the EoP and BoP are not set up a consolidated elements in the dimension, so that they would be calculated by aggregation.
      The data-load only happens for the NetMove (i.e. add & reduction). At the beginning of each year a BOP-value is written as well.

      So first my general question: Would you calculate the EoP via a hierarchy or via Rule? And what would does the Rule then look like for the BoP of the next period (i.e. day)?
      My second question: If I go for a rule solution only, how to I calculate the BoPs only for all days from the 2nd Jan onwards for each year in the calendar and how would I set up the EoP-rule?

      Thanks for any hints and pieces of advice.

      Br
      Bjoern

    • Ok,
      I changed the Movement Dimension so that EOP is parent of BoP and NetMove and thus is automatically calculated.

      The Rule for the continuation of BoP-Values is then the following:
      ['Movement':'BoP'] = IF(STET()==0,PALO.DATA("","",Palo.data("","#_Calendar","PreviousDay", !'Calendar', "~"), !'Version', !'Segment', !'ProjectType', !'Provider', !'ProductCategory', !'ProjectPhase', "EoP", !'KPI'),STET())
      In the Calendar I have an attribute which is filled with the previous day.

      Now here comes the current challenge:
      The Calendar starts at 2016-01-01. How do I exclude the rule for this day?
      Since it throws an error, because there is no previous day in the calendar.

      I tried it with IF(AND(!'Calendar'!="2016-01-01",STET()==0),... but this doesn't work.
      Obviously I have some kind of brainfart, since I cannot come up with a solution, how to exclude one single element out of a rule calculation.

      Any help is very much welcome.

      Thanks.

      Br
      Bjoern

    • I had something similar.... That first member in the Month dimension is an issue.

      What I came up with was to use a global subset on the month dimension, excluding the members that was irrelevant, i.e. the first member, in your case "2016-01-01" and "~" if such exists.

      The use the rule template functionality, i.e. "Query" in the rules.

      hope this helps.

    • Hi Ulrik,
      thats a good idea, thanks.
      In the meantime I made the following work around (for future reference, in case anybody needs it)
      I created the following rule for each year, because the performance was extremely bad, if I had the rule for all 6 years:
      ['Calendar':{${Elements}},'Movement':'BOP','KPI':{'SA', 'OE', 'CC'}] = IF(STET()==0,PALO.DATA("","",Palo.data("","#_Calendar","PreviousDay", !'Calendar', "~"), !'Version', !'Segment', !'ProjectType', !'Provider', !'ProductCategory', !'ProjectPhase', "EOP", !'KPI'),STET())

      I then made a cube extract on those KPIs to get the rule values and write them into the cube. Only the current year will have the rule active.
      The DWH backend is configured so that Netmove- & BOP-values are given for each day. Just the continuation until the end of year for the current year is needed
      This was the easiest on workload for the server and the performance and was acceptable on the manual interaction.