Performing YTD and other relative time calculations in TM1
Performing YTD and other relative time calculations in TM1
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
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
-
- 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
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
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?
- 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
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!
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
Rizwan Kaif
- 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
Please see the attached screen-shot of the sample Period dimension.
Hope this helps~!
Hope this helps~!
- Attachments
-
- Sample screen-shot of the Period dimension
- Period.JPG (25.19 KiB) Viewed 15402 times
Cheers!
Rizwan Kaif
Rizwan Kaif
Re: Performing YTD and other relative time calculations in TM1
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.
- 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
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
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
Rizwan Kaif
-
- MVP
- Posts: 3653
- 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
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.
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.
-
- 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
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!
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
Excel 2007
- 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
HighRoller, you are most welcome....I am delighted that my screen shots helped you.
Cheers!
Rizwan Kaif
Rizwan Kaif
Re: Performing YTD and other relative time calculations in T
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
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
- 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
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.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?
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
-
- 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
Hi Tutak,
If you have only one time dimension , you can use PeriodsToDate function to get the YTD.
If you have only one time dimension , you can use PeriodsToDate function to get the YTD.
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Performing YTD and other relative time calculations in T
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.
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.