Sum up last 6 month figure

[lkyow]

Hi,

I have one problem where i want to sum up last six month figure.

Example:
Let say current month is July, and the closing balance for july is to add figure for july+jun+may+apr+mar+feb.

I can use attribute (attrs) to do this , that is will add 6 attrs in the Date Dimension.
Attr 1: Prev Month
Attr 2: Prev 2 month
etc


Any other way to do this except using attribute?

Thanks

[Alan Kirk]

Hi,

I have one problem where i want to sum up last six month figure.

Example:
Let say current month is July, and the closing balance for july is to add figure for july+jun+may+apr+mar+feb.

I can use attribute (attrs) to do this , that is will add 6 attrs in the Date Dimension.
Attr 1: Prev Month
Attr 2: Prev 2 month
etc

Any other way to do this except using attribute?

As you've found you could do it through rules, but to be honest I wouldn't. Consolidations are going to be much better performance-wise, and considerably more transparent to users. I'd be more inclined to schedule a TI which ran overnight at month end and created/recreated a consolidation using the preceding 6 month elements.

[lkyow]

As you've found you could do it through rules, but to be honest I wouldn't. Consolidations are going to be much better performance-wise, and considerably more transparent to users. I'd be more inclined to schedule a TI which ran overnight at month end and created/recreated a consolidation using the preceding 6 month elements.

Hi Alan,

How do you do consolidation using preceding 6 month element? I am new to TM1

Thanks

[ellissj3]

hello,

I think Alan might be getting toward a TI to construct the DATE dimension overnight to take the following

if current month = "JUN"

-then last 6 month figure would be summation:
--JUN
--MAY
--APR
--MAR
--FEB
--JAN

There are many ways you can do this but a TI would be your best (most time efficient) bet.

[lkyow]

hello,

I think Alan might be getting toward a TI to construct the DATE dimension overnight to take the following

if current month = "JUN"

-then last 6 month figure would be summation:
--JUN
--MAY
--APR
--MAR
--FEB
--JAN

Hi ellissj3

But the Date Hierarchy in my cube should be
+FY 2011
+Q1
-Jan
-Feb
-Mar

+Q2
-Apr
-May
-Jun

etc


So, if i follow this Date Hierarchy that you suggested,
+FY2011
+Q1
+Jan11
- Jan11
- Dec10
- Nov10
- Oct10
- Sep10
- Aug10

etc

So I cant get the hierarchy that my cube needed as the first example? So how do I do it?

Thanks

[csjean]

Hi ellissj3

But the Date Hierarchy in my cube should be
+FY 2011
+Q1
-Jan
-Feb
-Mar

+Q2
-Apr
-May
-Jun

etc


So, if i follow this Date Hierarchy that you suggested,
+FY2011
+Q1
+Jan11
- Jan11
- Dec10
- Nov10
- Oct10
- Sep10
- Aug10

etc

So I cant get the hierarchy that my cube needed as the first example? So how do I do it?

Thanks

Hi lkyow,

The way we implemented this kind of behavior, is to leverage the ability of TM1 to support multiple hierarchies. Do not forget to adjust de weights of the "last 6 months" consolidations to 0 (so that number will not be consolidated in higher consolidations).

+FY2011 (+1)
+Q1 11 (+1)
-Jan11 (+1)
+Jan11 last 6 months (0)
- Jan11 (+1)
- Dec10 (+1)
- Nov10 (+1)
- Oct10 (+1)
- Sep10 (+1)
- Aug10 (+1)

Or you can also create a brand new hierarchy tree for all the "last 6 months" consolidations as in:

+FY2011 last 6 months
+Jan11 last 6 months (0)
- Jan11 (+1)
- Dec10 (+1)
- Nov10 (+1)
- Oct10 (+1)
- Sep10 (+1)
- Aug10 (+1)
+Feb11 last 6 months (0)
- Feb11 (+1)
- Jan11 (+1)
- Dec10 (+1)
- Nov10 (+1)
- Oct10 (+1)
- Sep10 (+1)


Hope this helps.

[lkyow]

Or you can also create a brand new hierarchy tree for all the "last 6 months" consolidations as in:

Hi csjean,

Can we create multiple hierarchy in a single Date Dimension?

[csjean]

Hi csjean,

Can we create multiple hierarchy in a single Date Dimension?

Yes. TM1 doesn't differentiate between time dimensions and other types of dimensions.

The only problem you might have is if you use Cognos 8 as a reporting tool. I just don't know if C8 supports multiple hierarchies for time dimension.
I know it does support multiple hierarchies for other types of dimensions.