Semi additive consolidation

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

    • Semi additive consolidation

      Hi all.

      I need to create a semi-additive consolidation, I have four dimensions, account, branch, month and measures.

      The measures dimension contains an element called "Actual" this element is a SUM if it's consolidating accounts or branches, but if it consolidates month in for examples quarters of year it has to be an AVERAGE, so "2013 quarter 1" has to be (Jan + Feb + Mar)/3, "2013 semester 1" has to be (Jan + Feb + Mar + Apr + May + Jun)/6, the month dimension is structured in the following way:

      - All years
      - Year.
      - Semester.
      - Quarter.
      - Month

      I have this approach using an enterprise rule:

      ['Actual'] = C:
      IF
      (
      PALO.ELEVEL("jdb","month",!'month') > 0,
      PALO.DATA
      (
      "jdb","cub1","Orig",!'account',!'branch',!'month'
      )/
      PALO.ECHILDCOUNT
      (
      "jdb",
      "month",
      !'month'
      ),
      PALO.DATA
      (
      "jdb","cub","Orig",!'account',!'branch',!'month'
      )
      )

      It takes another measure "Orig" that contains the metric with SUM for every level, and then it divides it / number of childs, for quarters is OK but then for semesters it divides the SUM / 2 (number of childs) which is not correct it would have to divide / 6 (number of base elements that compose a semester).

      Is there any way to create this behaviour?, is there any function that returns the number of base elements in certain dimension given a parent element?

      Thanks in advance, regards.
    • Hi,
      there is no such function yet, but You can create another measure and rule that will count number of base elements the same way.
      something like
      ['base_periods'] = B: 1
      For consolidated elements of dimension'month' you get it aggregated over all levels. I suppose you have no special weights in this dimensions.
      or even better would be to add new NUMERIC attribute 'base_periods' to dimension 'month' and read it from the attribute table.

      Jiri
    • Thanks a lot jjunek.

      I have a question here, if I add "['base_periods'] = B: 1" I think this will add a 1 for every month and for every branch - base account too. So if I SUM this new measure for every month I will obtain 3, and for every quarter 12, is this assumption correct?

      Could you please post an example of reading an attribute table from the enterprise rule?.

      Thanks in advance, regards.
    • If your 'month ' dimension has base elements identical to month then months will get 1
      Quarters will get 3. Semester will get 6, Year 12 etc.

      rule to get attribute 'base_periods' of element from dimension 'month' in database 'jdb'
      PALO.DATA("jdb", "#_month", "base_periods", !'month')

      your rule will be
      ['Actual'] = C:
      IF
      (
      PALO.ELEVEL("jdb","month",!'month') > 0,
      PALO.DATA("jdb","cub1","Orig",!'account',!'branch',!'month')/ PALO.DATA("jdb", "#_month", "base_periods", !'month'),
      PALO.DATA("jdb","cub","Orig",!'account',!'branch',!'month')
      )

      you can simplify it and write just:
      ['Actual'] = C: PALO.DATA("jdb","cub1","Orig",!'account',!'branch',!'month')/ PALO.DATA("jdb", "#_month", "base_periods", !'month'),
      It should be faster

      rule in cube '#_month' :
      ['base_periods'] = B: 1

      regards
      Jiri