Performing YTD and other relative time calculations in TM1

Performing YTD and other relative time calculations in TM1

Postby tutak » Mon Dec 21, 2009 5:28 pm

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
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

Postby Steve Joffe » Mon Dec 21, 2009 7:53 pm

You should create another hierarchy reflecting the YTD roll up you would like and then reference them into a report or view.
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

Postby tutak » Mon Dec 21, 2009 8:29 pm

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

Postby rkaif » Mon Dec 21, 2009 9:03 pm

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

Postby rkaif » Mon Dec 21, 2009 10:23 pm

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

Hope this helps~!
Attachments
Period.JPG
Sample screen-shot of the Period dimension
Period.JPG (25.19 KiB) Viewed 4831 times
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

Postby tutak » Mon Dec 21, 2009 10:58 pm

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

Postby rkaif » Tue Dec 22, 2009 12:09 am

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
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

Postby lotsaram » Tue Dec 22, 2009 2:45 am

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.
lotsaram
MVP
 
Posts: 2266
Joined: Fri Mar 13, 2009 11:14 am
Location: Switzerland
OLAP Product: TM1, CX
Version: 10.1 10.2
Excel Version: 2010 2013 365

Re: Performing YTD and other relative time calculations in TM1

Postby highroller » Wed Jan 20, 2010 2:10 pm

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
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

Postby rkaif » Wed Jan 20, 2010 11:06 pm

HighRoller, you are most welcome....I am delighted that my screen shots helped you.
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 T

Postby JayM » Tue Sep 20, 2011 3:08 pm

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
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

Postby qml » Tue Sep 20, 2011 4:49 pm

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
User avatar
qml
MVP
 
Posts: 677
Joined: Mon Feb 01, 2010 1:01 pm
Location: London, UK
OLAP Product: TM1
Version: 10.1.1 - 10.2.1
Excel Version: 2010

Re: Performing YTD and other relative time calculations in T

Postby zameelarif » Thu Sep 22, 2011 4:54 am

Hi Tutak,

If you have only one time dimension , you can use PeriodsToDate function to get the YTD.
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

Postby David Usherwood » Thu Sep 22, 2011 10:58 am

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.
David Usherwood
Site Admin
 
Posts: 1059
Joined: Wed May 28, 2008 9:09 am


Return to Cognos TM1

Who is online

Users browsing this forum: Baidu [Spider], Bing [Bot] and 2 guests

Loading