Performing YTD and other relative time calculations ...

zplwf

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

maxhyip

You should create another hierarchy reflecting the YTD roll up you would like and then reference them into a report or view.

messiahxu

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?

fs111

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!

wzsycdh

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

Hope this helps~!

AM哥

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

jhh6yf

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