Months and TimePeriod

Post Reply
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Months and TimePeriod

Post by manu0521 » Mon May 10, 2021 6:44 pm

Hi Users,

We a have a time dimension in our model it goes down from year till date. A user is requesting for another month only dimension along with the time dimension.

They wanted to build out ome reports where they can lay out months on the rows and years on the columns or vice versa.

We can do it using some calcualtions with one dimensions , but they wanted this so that they can slice an dice by month an year in same visualization .
How do you guys handle it and is it okay to ad a month only dimension .

Thanks,

User avatar
PavoGa
MVP
Posts: 529
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Months and TimePeriod

Post by PavoGa » Mon May 10, 2021 7:50 pm

Your profile says you are on version 9.5? Is that true?
Ty
Cleveland, TN

User avatar
paulsimon
MVP
Posts: 795
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Months and TimePeriod

Post by paulsimon » Mon May 10, 2021 9:33 pm

Hi

If you are actually on PA, then you should be able to use Named Hierarchies to do this. This is because with Named Hierarchies in a dimension you can show one Named Hierarchy on the rows and another on columns,

I assume that at present, you have one dimension with months like 2020-01, 2020-02 -----2020-12, 2021-01, 2021-02, etc

If you create a Named Hierarchy called Months with say February as a consolidation of 2020-02, 2021-02, March as a consolidation of 2020-03, 2021-03, etc. On its own in your single time dimension this is meaningless. I will assume that in your Time dimension you already have consolidations for months to years, so you have 2020 as a consolidation of 2020-01 ... 2020-12.

This should mean that you can select your Months Named Hierarchy on the columns, and your default hierarchy with the Year consolidations on the rows.

Although in your Months Named Hierarchy the February consolidation is made up of 2020-02, 2021-02, etc, when you combine this with rows that show eg 2020 and 2021, then you should see that in the cell for February with 2020, you only see the figure from 2020-02.

If you can give it a try and let me know if it works, I will update my time dimension generator to incorporate this. So far it has not really been a requirement at my current client.

If you are not yet on PA, then the only real option to do this is a virtual cube, ie you create a cube with separate month and year dimensions and you rule the data cross to it.

Regards

Paul Simon

manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: Months and TimePeriod

Post by manu0521 » Tue May 11, 2021 1:11 am

Hi Paul ,

We are on PA only , the current time dimension we have is like below

Year ,Month and Date .

2020
Jan 2020
20200101

2021
Jan 2021
20210101

so do you want me to create a alternate hiearchy with just months in it .

Months
what months should it have ,

I wanted something like this
Jan Feb Mar Apr etcc. Dec
2020
2021
2022

so my current month is Jan 2020 or 202001 , how can i make just jan , feb on the hierarchy should it be an sttribute on the months ..

Thanks,

User avatar
paulsimon
MVP
Posts: 795
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Months and TimePeriod

Post by paulsimon » Tue May 11, 2021 9:22 pm

Hi Manu

You need to create a proper Named Hierarchy, not just an alternate hierarchy. Check the TI Reference guide.

Do not bother with Attribute Hierarchies. Create a Named Hierarchy, and use HierarchyElementInsert and HierarchyElementComponentAdd to build the month consolidations.

Regards

Paul Simon

User avatar
paulsimon
MVP
Posts: 795
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Months and TimePeriod

Post by paulsimon » Thu May 20, 2021 10:05 pm

Hi manu

I have updated my Month Dimension Generator to include a Period Totals Named Hierarchy. This includes consolidations giving the sum of the same period across all years, and a Full Year consolidation of those periods. With this, in PAX or PAW you can use, for example, the Periods and Full Year subset on the F4_Per_Tot Named Hierarchy on the Columns and the Year level of the F4_YHQM Named hierarchy on the Rows. (The F4 prefix means that the Financial Year starts in April, since obviously what is considered Period P01 is different if the company uses eg the Calendar Year as its Financial Year. The generator allows you to generate dimensions that include the Calendar Year and Financial Year(s) starting in any period you want.

Image

It is free to download via our web site [url]http:www.successcubed.co.uk[/url]

I would hope that this finally puts the one or two time dimension debate to rest, but I doubt it.

Regards

Paul Simon
Attachments
Periods on Cols Years on Rows Two Dims from One.jpg
Periods on Cols Years on Rows Two Dims from One.jpg (58.48 KiB) Viewed 3209 times

User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Months and TimePeriod

Post by Harvey » Thu Jun 03, 2021 6:51 am

paulsimon wrote:
Thu May 20, 2021 10:05 pm
I would hope that this finally puts the one or two time dimension debate to rest, but I doubt it.
No, it does. Best of both worlds.
Take your TM1 experience to the next level - TM1Innovators.net

lotsaram
MVP
Posts: 3499
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Months and TimePeriod

Post by lotsaram » Thu Jun 03, 2021 9:30 am

Harvey wrote:
Thu Jun 03, 2021 6:51 am
paulsimon wrote:
Thu May 20, 2021 10:05 pm
I would hope that this finally puts the one or two time dimension debate to rest, but I doubt it.
No, it does. Best of both worlds.
For actuals reporting and modelling I agree.
But split time dimensions still have a place in data entry which is enabled via spreading. E.g. use case relative proportional spread from Version A Year N to Version B Year N+1. With a single time dimension you can't do this.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Months and TimePeriod

Post by Harvey » Fri Jun 04, 2021 4:26 am

Yeah fair enough. I tend to use and encourage data copy processes for that kind of thing, but it is nice to have the data spreading option in many cases.
Take your TM1 experience to the next level - TM1Innovators.net

Post Reply