YTD Grouped in TM1?

[ChrisF79]

We currently have YTD Grouped in our Cognos 8 cubes (a time period grouping of YTD, PYTD, the diference between the two, and the growth %). Now that we're pushing TM1 cubes out through Cognos 10, we'd like to still have that YTD Grouped in those Cognos cubes. Therefore, I need to create it in TM1. Any clever way of doing this?

[David Usherwood]

You'd use consolidations and rules (as for any calculation in TM1).
You didn't say whether you have one or two time dimensions - I suspect one, since most relational warehouses do.
If you have two time dimensions (Year and Month), YTD and PYTD are pretty easy. If not, you've got more work to do, especially as you extend the dimension forward.

http://www.tm1forum.com/viewtopic.php?f=3&t=207
Is a good debate about the pros and cons of the two approaches.

[paulsimon]

Hi Chris

Do you by any chance have both the Monthly and the YTD figures in the database that you use to drive Cognos BI?

If you only have YTD figures, then please disregard the rest of this.

I understand that this is a fairly common technique with Cognos BI as it can be slow when adding up months to get a YTD figure.

However, with the fast consolidation in TM1, this is not necessary.

You can just create the consolidations to roll up the Monthly figures to get the YTD.

I then typically use another dimension called Time Type to do the growth

This has elements for

Base
Period Growth %

The Growth is calculated by Rules using Attributes on the Time Dimension to get the Previous Period. You don't need the Growth since that is just the monthly value.

I use a combination of some VBA routines and TI processes that will generate the necessary consolidations for YTD, CTD, QTR, etc, in the Time Dimension. The parameters are just driving by selections in the spreadsheet that holds the VBA

Regards

Paul Simon