YTD concept in TM1

[DavidSmith]

HI
As a TM1 newbie I wondered how you get a YTD value in a cube

Assume I have a cube with the following dimensions, (which is a very common occurance):

P&L (Elements - Sales, Costs, Margin)
Months (Elements - Jan-Dec)
Versions (Elements - Actual, Budget, Variance)
YTD (Elements - Mth, YTD)

How do I get my months to Cumulate into YTD as EP would?

Cheers
David

[MichelZ]

Hi David,

A common way to model this in TM1 is to create the YTD periods as rollups in the time dimension.
If you add roll-ups like Ytd Feb (consisting of Jan and Feb), Ytd Mar (consisting of Jan to Mar or consisting of Ytd Feb and Mar), etc. then the Ytd values will be automatically calculated from the data on the Jan to Dec elements.

Michel

[DavidSmith]

Thanks Michel
So in effect you have a bigger dimension like:

Jan, Feb, ... Dec, Full Year, Jan Ytd, Feb Ytd ... Dec Ytd

One question though, how would you display something like this for Jan:

                  Mth                           YTD
          Act    Bud    Var          Act    Bud    Var
Sales
Costs
Margin

I suppose you could set up subsets and views, just with Jan Mth and Jan YTD, but that seems a bit fiddly
Cheers
David

[raviB]

hI

Eeasiest (and most efficient) is to have 12 n type elements for the months.

Then you add consolidations for the YTD's.

YTD Jan is the sum of Jan.
YTD Feb is the sum of Feb and YTD Jan.
YTD Mar is the sum of Mar and YTD Feb.
And so on.
A TI process could create the structure but in the end, doing this manually is not the end of the world

You could also stick with 12 simple elements for the months, and use another dimension to have a Cumul element (calculated with rules). It's more heavy because of the rules. Yet, comparing months and YTD's is easier in a view if you do it like this.

Regards,
Ravi

[DavidSmith]

Hi Ravi
Thanks for the reply
Yes, I understand that hierarchy is more efficient than rules

Assuming I did want to go the rules route, is there some kind of clever rule that can be used rather than coding each Cumul month to be the sum of the preceding months?

Thanks
David

[Mehul]

Hi David,

You can use function like DIMNM & DIMIX for YTD.
For eg., you have a cube which have 2 dimensions
1 Months
2 Measures (Input & Result)

you can use the rule as

['Result']  = N: IF (DIMIX('Months_T', !Months_T) = 1, ['Input'], DB('YTD',DIMNM('Months_T',DIMIX('Months_T', !Months_T)-1), 'Result')+['Input']);

Mehul

[dusherwo]

Dimix is not a good idea unless you can guarantee that you will be able to keep your elements in the order you expect. Next and Prior attributes are sounder.