You are not logged in.

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

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

Phil_Ro

Master

  • "Phil_Ro" is male
  • "Phil_Ro" started this thread

Posts: 39

Date of registration: Aug 24th 2009

Location: Frankfurt, Germany

Occupation: BI Consulting

Hobbies: Flying, Biking

  • Send private message

1

Tuesday, May 29th 2012, 10:46am

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

Hi,

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 ?

a_malinowski

Master

  • "a_malinowski" is male

Posts: 72

Date of registration: Nov 12th 2008

Location: Berlin, Germany

  • Send private message

2

Tuesday, May 29th 2012, 5:05pm

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
2
3
4
5
6
7
8
9
10
11
12

    ['UnitsLTM','Apr'] = PALO.DATA("Demo","Sales",!'Products',!'Regions',"May",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Jun",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Jul",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Aug",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Sep",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Oct",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Nov",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Dec",PALO.EPREV("Demo","Years",!'Years'),!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Jan",!'Years',!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Feb",!'Years',!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Mar",!'Years',!'Datatypes',"Units")
+PALO.DATA("Demo","Sales",!'Products',!'Regions',"Apr",!'Years',!'Datatypes',"Units")
    You have to write such a rule for every month.
Hope this help you.
Andreas Malinowski
FORECAST Unternehmensplanungen GmbH, Berlin, Germany
www.forecast.de

Phil_Ro

Master

  • "Phil_Ro" is male
  • "Phil_Ro" started this thread

Posts: 39

Date of registration: Aug 24th 2009

Location: Frankfurt, Germany

Occupation: BI Consulting

Hobbies: Flying, Biking

  • Send private message

3

Wednesday, May 30th 2012, 5:16pm

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:

2012-March_MTD
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?

jjunek

Jedox Team

  • "jjunek" is male

Posts: 58

Date of registration: Sep 16th 2009

Location: Prague

Occupation: PALO OLAP Server architect

  • Send private message

4

Tuesday, June 12th 2012, 11:39am

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']
+['Jun','Years':offset(-1),'Units']
+['Jul','Years':offset(-1),'Units']
+...
+['Apr,'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

Similar threads

Used tags

LTD

Rate this thread