Page 1 of 1

Performing YTD and other relative time calculations in TM1

Posted: Mon Dec 21, 2009 5:28 pm
by tutak
Hi,
I am new to TM1 and looking for a solution to handle relative time dimensions in the cubes. I searched the whole forum, and it seems like there is a lot of good information about the topic, but for a person new to the tool ,it was not easy to understand the conversations clearly.

I am trying to create a basic income cube with accounts and locations dimensions. The data comes at a monthly granularity. I see that I can create a cube with years and months dimension and create YTD calculation manually by changing the calculations in the Month dimension manually each month. But obviously this is not the best approach.

What is the best approach to handle this kind of requirement, where we need to have a YTD calculation in the cube which shows the right amount based on the month that we are in?

Can you give me some clear direction or at least refer a good documentation to me about this topic.

Thanks a lot

Re: Performing YTD and other relative time calculations in TM1

Posted: Mon Dec 21, 2009 7:53 pm
by Steve Joffe
You should create another hierarchy reflecting the YTD roll up you would like and then reference them into a report or view.

Re: Performing YTD and other relative time calculations in TM1

Posted: Mon Dec 21, 2009 8:29 pm
by tutak
Steve Joffe wrote:You should create another hierarchy reflecting the YTD roll up you would like and then reference them into a report or view.

I'd really appreciate it if you gave little bit more details. When you say alternate hierarchy. do you mean within the same dimension? If yes, what should be my calculations? Can you give an example of such a dimension?

Re: Performing YTD and other relative time calculations in TM1

Posted: Mon Dec 21, 2009 9:03 pm
by rkaif
Hi Tutak,

You Period/Time dimension should look like something like this:

2009
>2009.Jan
>2009.Feb
>2009.Mar
>2009.Apr
>.....
>.....
>2009.Dec

then within the same Dimension you created your YTD hierarchy for each year like:

2009YTD
>2009YTD.Jan
>2009.Jan
>2009YTD.Feb
>2009.Jan
>2009.Feb
>2009YTD.Mar
>2009.Jan
>2009.Feb
>2009.Mar

and so on....

Hope it helps!

Re: Performing YTD and other relative time calculations in TM1

Posted: Mon Dec 21, 2009 10:23 pm
by rkaif
Please see the attached screen-shot of the sample Period dimension.

Hope this helps~!

Re: Performing YTD and other relative time calculations in TM1

Posted: Mon Dec 21, 2009 10:58 pm
by tutak
rkaif wrote:Please see the attached screen-shot of the sample Period dimension.

Hope this helps~!

Rizwan, thanks for the screen shot. in this picture, is YTD2002 a sum of YTD200201, YTD200202, ...? If yes, then doesn't it have 200201 more than one time?

I think where I am struggling is more on the reporting side. Is there a way to have such a dimension where YTD is automatically calculated based on the current month that we are in (system date)?

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
YTD -> This is an automatic calculation. based on the current month it is a total of YTD months.

Re: Performing YTD and other relative time calculations in TM1

Posted: Tue Dec 22, 2009 12:09 am
by rkaif
Tutak,

In the sample as you can see YTD2002, YTD200201, YTD200202 etc are the consolidated elements. 200201 is a n-th level element and is appearing more than once in the screenshot due to the consolidations which we are creating in the Period dimension.

You have to load the monthly data once for each month and the Consolidations which we are creating here will do the job for you. For example if you are in month of March then you will have data in Jan, Feb and March. Other months (April & onwards) will have zero. So in March you will be able to see consolidated values for YTDJan, YTDFeb and YTDMarch & other YTDMonths will have zero

Re: Performing YTD and other relative time calculations in TM1

Posted: Tue Dec 22, 2009 2:45 am
by lotsaram
Hi Tutak,

There are a few ways to do what you want to do. You can have 'YTD' be an N element in your month dimension and have a rule based approach that would look at a value in a system cube and then direct to the appropriate consolidation. Your rule might look something like ...
['YTD'] = N: DB('your cube', !Dim1, !Dim2, !Dim3, DB('Sys Info', 'Last Act Month', 'String') | 'YTD', !Dim5);

If you go for this you would need to implement this rule in every cube that you want to have the dynamic YTD reporting active.

Otherwise you could have 'YTD' as a consolidated node that is maintained automatically and changes to have the appropriate children added. Or you could have a YTD construct similar to rkaif's example of the 'YTD2002' hierarchy and each month automatically maintain the weightings of each YTD consolidation child so that only the appropriate YTD consolidation has a weight of 1 and the rest are 0 weighted, this avoids any double counting.

Hope that wasn't too confusing for you.

Re: Performing YTD and other relative time calculations in TM1

Posted: Wed Jan 20, 2010 2:10 pm
by highroller
this post helped me get YTD consolidating in the period dimension following your screenshots. Thank you!!!

I am still interested in learning how to do it in a rule (although I understand the pro's and con's) because before we were trying to do it as another measure element like the period balance instead of this a approach which is doing a consolidation in the period utilizing a new hierarchy. I am going to do a little more reading and researching and if I get stuck ill post a message but thank you very much... this really helped!

Re: Performing YTD and other relative time calculations in TM1

Posted: Wed Jan 20, 2010 11:06 pm
by rkaif
HighRoller, you are most welcome....I am delighted that my screen shots helped you.

Re: Performing YTD and other relative time calculations in T

Posted: Tue Sep 20, 2011 3:08 pm
by JayM
Hi I am new to this.

I saw your attachment. I have Time Dimension which has everything separately rolling up to Year i.e, Year <-- quarter <-- Month which I have uploaded from a excel source file.

Do I have to create new column called YTD in excel and then rerun the process in TM1 or I can create the new element called YTD in the existing cube?

Please help me as I am not able to get this.

Thanks,
Jay

Re: Performing YTD and other relative time calculations in T

Posted: Tue Sep 20, 2011 4:49 pm
by qml
JayM wrote:Do I have to create new column called YTD in excel and then rerun the process in TM1 or I can create the new element called YTD in the existing cube?
You can add new consolidated YTD elements to the dimension at any time and they will start working immediately without any other changes to the model.
How you decide to add them to your dimension would generally depend on how you maintain it normally - through DImension Editor, Dimension Worksheet or a TI process.

Re: Performing YTD and other relative time calculations in T

Posted: Thu Sep 22, 2011 4:54 am
by zameelarif
Hi Tutak,

If you have only one time dimension , you can use PeriodsToDate function to get the YTD.

Re: Performing YTD and other relative time calculations in T

Posted: Thu Sep 22, 2011 10:58 am
by David Usherwood
PeriodsToDate is an MDX function.
My experience of MDX with TM1 suggests that using this will be slower than using rules, and much slower than using consolidations. But I'd be interested to hear of any test results.