How to build Last Year to Date values ? e.g. April 2011 - February 2012 depending on current Date

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

  • How to build Last Year to Date values ? e.g. April 2011 - February 2012 depending on current Date


    I need to model my Date Dim with a value like year-to-date, but not as a static consolidation of the previous months of actual year, but as a sum of the last 12 Months.
    So it would have to be dynamically depending on the current month.

    e.g. it's April so my LTD value would consist of
    May 2011 -Dec. 2011, Jan 2012 - April 2012

    Is there any way to get this working ?
  • Hi,

    ther are two ways:
    1. If you are using one dimension for date, e.g. "Dec 2011", "Jan 2012", ..., you can use static consolidation.
    2. If your are using two dimensions for date (month and year), you can use rules. But this would be longer ;)
      Here you can see a sample rule for April.

      Source Code

      1. ['UnitsLTM','Apr'] = PALO.DATA("Demo","Sales",!'Products',!'Regions',"May",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      2. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Jun",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      3. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Jul",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      4. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Aug",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      5. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Sep",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      6. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Oct",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      7. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Nov",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      8. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Dec",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
      9. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Jan",!'Years',!'Datatypes',"Units")
      10. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Feb",!'Years',!'Datatypes',"Units")
      11. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Mar",!'Years',!'Datatypes',"Units")
      12. +PALO.DATA("Demo","Sales",!'Products',!'Regions',"Apr",!'Years',!'Datatypes',"Units")
      Display All
      You have to write such a rule for every month.
    Hope this help you.
    Andreas Mueller
    FORECAST Unternehmensplanungen GmbH, Berlin, Germany
  • Thanks a lot for your input.

    In this example we have a Months and Years Dim and we get the coresponding value of last year by the Previous function.

    But I'm stuck with a single Date Dim which goes down to Day level.
    So I have a motnh level which is unique (year-month).

    Do I get it right that in this case I can have static consolidates like:

    which includes 12 months: 2012-03 .. 2012-01,2011-04..2011-12

    So I end up with a sum of (number of Years) * 12 consolidated Elements, which I hav to define manually.
    My Date Dim comes from the Date Extract, is there a way to automate those consolidations?
    There is a "Time-To-Date Levels" in the calendar extract, may this be helpful?
  • Hi,

    in 3.3 version of palo when writing rules accessing cells from the SAME cube you can use shortened version of PALO:EPREV

    ['UnitsLTM','Apr'] = ['May','Years':offset(-1),'Units']

    This syntax version is static and offset function can contain only signed integer constant - it should be faster and it is definitely shorter.
    Unfortunately there is no simple way to write a "loop" in rules in any MOLAP databases I know. You can use for example excel to generate the rule and paste it into the editor.

    If your Date dimension contains months and years and if the base elements are ordered from 2012-01(at position 0), 2012-02,...2013-11,2013-12 (with no consolidations in between)
    you can write a generic rule:
    ['value-12M'] = ['value']+['Date':offset(-1),'value']+['Date':offset(-2),'value']+['Date':offset(-3),'value']+...+['Date':offset(-11),'value']
    you don't have to care about testing boundaries, because version with offset returns null if the position of element is out of limits