Performing YTD and other relative time calculations in TM1

Post Reply
tutak
Posts: 15
Joined: Mon Dec 21, 2009 4:50 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Performing YTD and other relative time calculations in TM1

Post 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
Steve Joffe
Posts: 5
Joined: Wed Dec 16, 2009 5:55 am
OLAP Product: Cognos Tm1
Version: 9.4 MR2
Excel Version: 2007

Re: Performing YTD and other relative time calculations in TM1

Post 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.
tutak
Posts: 15
Joined: Mon Dec 21, 2009 4:50 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Performing YTD and other relative time calculations in TM1

Post 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?
User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Performing YTD and other relative time calculations in TM1

Post 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!
Cheers!
Rizwan Kaif
User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Performing YTD and other relative time calculations in TM1

Post by rkaif »

Please see the attached screen-shot of the sample Period dimension.

Hope this helps~!
Attachments
Sample screen-shot of the Period dimension
Sample screen-shot of the Period dimension
Period.JPG (25.19 KiB) Viewed 15180 times
Cheers!
Rizwan Kaif
tutak
Posts: 15
Joined: Mon Dec 21, 2009 4:50 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Performing YTD and other relative time calculations in TM1

Post 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.
User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Performing YTD and other relative time calculations in TM1

Post 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
Cheers!
Rizwan Kaif
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Performing YTD and other relative time calculations in TM1

Post 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.
highroller
Posts: 3
Joined: Thu Jan 14, 2010 11:02 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Performing YTD and other relative time calculations in TM1

Post 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!
TM1 9.4.1
Excel 2007
User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Performing YTD and other relative time calculations in TM1

Post by rkaif »

HighRoller, you are most welcome....I am delighted that my screen shots helped you.
Cheers!
Rizwan Kaif
JayM
Posts: 23
Joined: Wed Sep 14, 2011 3:21 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Performing YTD and other relative time calculations in T

Post 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
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Performing YTD and other relative time calculations in T

Post 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.
Kamil Arendt
zameelarif
Posts: 13
Joined: Tue Apr 07, 2009 8:46 am
Version: 9.1.3
Excel Version: 2007

Re: Performing YTD and other relative time calculations in T

Post by zameelarif »

Hi Tutak,

If you have only one time dimension , you can use PeriodsToDate function to get the YTD.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Performing YTD and other relative time calculations in T

Post 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.
Post Reply