Creating a Dynamic Subset

Post Reply
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Creating a Dynamic Subset

Post by Hector2005 »

Hello friends, hope everyone is good enough.

I have a question about subsets. I built a model using IBM Cognos Planning Analytics TM1 (Performance modeler), in this model I have two dimensions for time : Month Dimension and Year Dimension
Month
-Q1
Jan
Feb
Mar
+Q2
+Q3
+Q4

Year:
2017
2018
2019

My Problem: If I want to make a subset such as (Capture.JPG)
Hint: if I have current year(2017) that has YTD (From Jan to August) and FYR (From August to December) and the next year has (2018) that has (Q1, Q2, Q3, Q4). I want to make this subset automated this mean if the current year is 2018 So, It will have YTD, FYR and 2019 will have Q1,.., Q4
and So on.

If anyone has any new Ideas to reach our target easily, Sure you are welcome.

Thanks a lot,
Best Regards,
Hector.
Attachments
I want to show this time slice as a subset
I want to show this time slice as a subset
Capture.JPG (16.71 KiB) Viewed 2996 times
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Creating a Dynamic Subset

Post by Wim Gielis »

Creating a subset happens in 1 dimension.
A subset (usually) has little knowledge of what happens in the other subsets of cube dimensions.

It depends on the client used. In Excel and TM1 Web, you can use Excel cell references to come up with logic to return the required elements.
In the cubeviewer this will be a lot harder to do. To begin with, 2 dimensions will not be easy as 1 merged dimension for this exercise.
This is not to say that you should change your cube to 1 dimension instead of 2 - there are more factors in the mix.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Creating a Dynamic Subset

Post by Hector2005 »

Wim Gielis wrote: Sat Feb 24, 2018 11:49 am Creating a subset happens in 1 dimension.
A subset (usually) has little knowledge of what happens in the other subsets of cube dimensions.

It depends on the client used. In Excel and TM1 Web, you can use Excel cell references to come up with logic to return the required elements.
In the cubeviewer this will be a lot harder to do. To begin with, 2 dimensions will not be easy as 1 merged dimension for this exercise.
This is not to say that you should change your cube to 1 dimension instead of 2 - there are more factors in the mix.
Thanks a lot for your concern Really appreciated.

Fine, I have two questions.
Question One: If I will use TM1 Web what is your Ideas to do this subset if I have two dimensions?
Question Two: If I will change the structure to be one dimension instead of two dimensions How can I do this?(In cube viewer and TM1 Web)

Thanks a lot,
Best Regards,
Hector.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Creating a Dynamic Subset

Post by Wim Gielis »

Q1: use Excel references and IF formulas to come up with the right elements. Do you put these months/years in the columns of a websheet ? Or where exactly do you use those 2 subsets ?
Q2: I would use a SUBNM to aks a period/year combination by the user. Then make all your subsets once manually and populate them with the correct elements and alias. Or use a TI process to automate it. Name your subsets in a consistent way, containing the starting period/year combination in its name. Coming up with MDX formulas might be possible but very difficult I guess.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Creating a Dynamic Subset

Post by Hector2005 »

Wim Gielis wrote: Sat Feb 24, 2018 12:07 pm Q1: use Excel references and IF formulas to come up with the right elements. Do you put these months/years in the columns of a websheet ? Or where exactly do you use those 2 subsets ?
Q2: I would use a SUBNM to aks a period/year combination by the user. Then make all your subsets once manually and populate them with the correct elements and alias. Or use a TI process to automate it. Name your subsets in a consistent way, containing the starting period/year combination in its name. Coming up with MDX formulas might be possible but very difficult I guess.
Sorry for my delay in this respond, and grateful enough for your aid. I made an intermediate solution.
Subset in the month dimension has ytd, fry, q1,q2,q3,q4
When I select a year from year filter I will calculate q1,...q4 for the next near. This solve the problem.


Please if you can help in this.
http://www.tm1forum.com/viewtopic.php?f=3&t=13906
Post Reply