YTD Calculation

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

• YTD Calculation

Hello all / Hallo ihr alle,

I have the impression that this question was asked before, but the answers I found do not seem to fit my problem completely

___________________________________
Let's consider the following cube:
Period: Period 1, Period 2, ... , Period 12
View of the data: YTD or Periodic
Measures: "Cumulative" attribute set to 0 or 1

Using a rule, I would like to calculate a YTD for the measures that have 1 as an attribute, and the periodic value for measures that have a 0. Example:

'Sales' has a 1 as cumulative attribute:
Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6
100 110 100 110 130 125

Those are Periodic values. That means that Period 6 / YTD / Sales = 675

Now let's consider an 'Employees State' measure (with a 0 as cumulative attrib):
Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6
20 21 30 28 30 29

Of course Period 6 / YTD / Employees State is not the sum of all the 6 periods, but the Period 6 number, ie 29.

In my rule, I already managed the attribute part, but I do not have a clue with the calculation rule. Is this possible in Palo or not ?

Please apologize for the length of this email.

Thanks in advance ! / Vielen Dank vorab!
laloune
laloune

Post hoc, non est propter hoc

The post was edited 1 time, last by laloune ().

• Hello all,

actually I may have found a solution (not very dynamic but this customization is not likely to change within a year)

I created 12 rules that calculate the YTD for each Period:

['YTD','1'] = ['Periodic','1'] => for January YTD = Periodic

['YTD','2'] = IF(PALO.DATA("PILOR","#_Account","Cumulable",!'Account') == "No",['Periodic','2'],['YTD','1'] + ['Periodic','2']) => test of the "Cumulative" attribute then calculation of the YTD using the Periodic value

I wonder whether there is an easier solution but it does work this way
laloune

Post hoc, non est propter hoc

The post was edited 2 times, last by laloune ().

• Hi Laloune

I am not sure that my solution is simpler, but it does the calculation in a single rule.

In my periods dimension I have both months and quarters, so I cannot use the PALO.EPREV function. To get round this I have created an attribute on the periods dimension called previous period to identify what the correct previous period is, fo January, Quarter 1 and Year this is set to "NA".

My dimensions are Brands, Department and Product, Measures, Periods, Years, scenario and Consolidation (where Consolidation contains the 3 elements: Period, YTD and R12).

In the first part of the rule I check the Cumul type attribute on the measure dimension which flags whether the measure element can be consolidated or not i.e. Turnover, Costs, etc. the value is Y, stock, headcount the value is N

['YTD'] =
IF(PALO.DATA("MA_P_and_L", "#_Measures", "Cumul type", !'Measures') != "N", => Checks the cumulation attribute
IF(PALO.DATA("MA_P_and_L", "#_Periods", "Previous Period", !'Periods') != "NA", => Checks whether it is not the first Period element
PALO.DATA("MA_P_and_L", "P_and_L", !'Brands', !'Department and Product', !'Measures',
PALO.DATA("MA_P_and_L", "#_Periods", "Previous Period", !'Periods'), => get the previous period element
!'Years', !'Scenario', "YTD") => statement gets the YTD value of the previous period

+ PALO.DATA("MA_P_and_L", "P_and_L", !'Brands', !'Department and Product', !'Measures', !'Periods', !'Years', !'Scenario', "Period")
=> Adds on the current period value

, PALO.DATA("MA_P_and_L", "P_and_L", !'Brands', !'Department and Product', !'Measures', !'Periods', !'Years', !'Scenario', "Period"))
=> If it is the first period element take the Period value

, CONTINUE())
=> if it is not a cumulation measure e.g. Stock. Do not calculate value here, but allow to be calculated in a later rule

I don't know if this is any use - let me know if you need any further clarification.

Janet
• Hi Janet,

great idea to calculate YTD in this way. It works very good. I'm interested in the "later rule" for calculation of the non cumulation measures. Do you have an example for me?

Thank you very much!

Regards Etan
• Hi Janet and all...
I also - beside the regular PTD ( period-to-date ) view - need to get a YTD ( year-to-date ) view in my PeriodView dimension...
and same as Janet, I have month rolling into quarter and then FullYear...
So I found the above logic quite promising and it got me started... though I can't get it to work...

My dimensions are (in order):
- Year (2008, 2009, 2010, 2011, 2012, 2013, etc)
- Period (FullYear, Q1, Jan, Feb, Mar, Q2, Apr, May, Jun, Q3, Jul, Aug, Sep, Q4, Oct, Nov, Dec)
- PeriodView (PTD, YTD, later QTD)
- Scenario (Actual, Budget, Target)
- Currency (EUR, GBP, CHF, etc...)
- ProductGroup (AllProd, ProdGroup1, Prod1001, Prod1002, Prod1003, ProdGroup2, Prod2001, Prod2002, Prod2003
- Organization (TotalOrg, OrgNorth, NorthOffice1, NorthOffice2, NorthOffice3, OrgEast, EastOffice1, EastOffice2, EastOffice3, etc...)
- Account (chart-of-account, KPI_accounts)

Then what I did was: create an attribute called "PriorPeriodYTD" in which I've wrote the "prior period" for the YTD view when applicable or "none" for Jan, Q1 and FullYear...

And then I created following rule (without the "__"):
['YTD'] = IF(PALO.DATA("PaloSERVER/PaloDB","#_Period","PriorPeriodYTD") == "none",
PALO.DATA("PaloSERVER/PaloDB","PaloCUBE",!'Year',
_______________________________________________PALO.DATA("PaloSERVER/PaloDB","#_Period","PriorPeriodYTD",!'Period'),
______________________________________________________"YTD",!'Scenario',!'Currency',!'ProductGroup',!'Organization',!'Account')+
PALO.DATA("PaloSERVER/PaloDB","PaloCUBE",!'Year',!'Period',"PTD",!'Scenario',!'Currency',!'ProductGroup',!'Organization',!'Account'),
PALO.DATA("PaloSERVER/PaloDB","PaloCUBE",!'Year',!'Period',"PTD",!'Scenario',!'Currency',!'ProductGroup',!'Organization',!'Account'))

my logic: check for prior period in "PriorPeriodYTD" attributes, if PriorPeriodYTD<>"none" then there is a prior period, so take the YTD of this prior period and add the period's PTD... and if PriorPeriodYTD="none" then just take this period's PTD value - which is the case for Jan, Q1 and FullYear...

Now I only get a "#Name?" and don't really know how to proceed...
Can anyone help and maybe let me know what I'm doing wrong?
Thx a million!

The post was edited 5 times, last by cab ().

• In your check for "none" shouldn't it be

PALO.DATA("PaloSERVER/PaloDB","#_Period","PriorPeriodYTD") == "none"

See lalounes post.
• Hi All,

how many values, dimensions and elements do you have in the respective cubes? Is this quick enough with Community Edition?

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• Hi kratzer, tish1 and all,
kind of figured it out in a smaller test cube - with only Year, Period, PeriodView and Account dimension...
the rule that made it work is this (again without the "_"):

['YTD'] = IF(PALO.DATA("PaloDB", "#_Period", "PriorPeriodYTD", !'Period') == "none",
PALO.DATA("PaloDB","PaloCUBE", !'Year', !'Period', "PTD", !'Account'),
PALO.DATA("PaloDB","PaloCUBE", !'Year',
____________________________________PALO.DATA("GSC", "#_Period", "PriorPeriodYTD", !'Period'),
___________________________________________ "YTD", !'Account')+
PALO.DATA("PaloDB","PaloCUBE", !'Year', !'Period', "PTD", !'Account'))

I also corrected and crossed out the errors in above post - no need for the PaloSERVER in the rule...

@kratzer: yes, should have been "=="...
@ tish1: also yes, it's a lot of dimensions and tons of members... I'm actually replicating a larger Essbase cube which's dimension setup isn't what i need and hence doesn't provide the right POV... speed in PTD view it works fine... only with the YTD rule, things seems to get... hmmm... a bit rocky... so I'm not sure if i can really implement this YTD rule... any suggestions though how to speed things up - maybe making the dynamic YTD calc rule a pre-calc values ineed...? or does it just take a while til all YTD values are calculated...? so many questions...

lastly, posts here in the rules sections saying that rules won't work if looked-up values inside the rule are themselves calculated values are false, right?

The post was edited 1 time, last by cab ().

• does anyone know or have an idea if using Markers would make this YTD calc more feasible?
So far it unfortunately wasn't feasible to use above rule as there are - as tish1 had suspected - too many fields to be calced...

So I was wondering if using MARKERs would solve this...

Thx for any input again!
• Hi,

cab wrote:

does anyone know or have an idea if using Markers would make this YTD calc more feasible?

I'd give it a try. But be careful, you can't use if-statements there.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH