Dynamic subsets on Time dimension

Post Reply
Rtel
Posts: 59
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Dynamic subsets on Time dimension

Post by Rtel »

Hello, I want to write MDX query to create a dynamic subset on time dimension

Time dimension element could be a Date or Week number

According to me, one of the way is to create an attribute for each of these elements and filter elements based on attribute value.

Is there any other way to create this subset where I can provide two dates or two week numbers as parameters and all the elements in this range provided will be part of the subset ?

Thanks

Rtel
Wim Gielis
MVP
Posts: 3105
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: Dynamic subsets on Time dimension

Post by Wim Gielis »

Typically, you would have a (small) input cube where the parameter values are entered (week numbers).
Then, an MDX query would filter on the elements. Yes, an attribute would be a good idea. If you take up the week number, formatted ww, in the attribute, this would not be terribly difficult. Getting the values from the cubes might not be easy though.
Maybe you would be better off by using a TI process, to either set up the MDX query (and use a syntax that is simpler to retrieve the parameter values), or loop through the dimension and fill the subset in a static way. If you are new to all this, use the last approach and gradully step up to the other more dynamic methods.
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
howard40116
Posts: 12
Joined: Tue Oct 02, 2018 5:35 am
OLAP Product: PAL
Version: PAL 2.0.4
Excel Version: excel 2010
Contact:

Re: Dynamic subsets on Time dimension

Post by howard40116 »

1.Create Parameter Cube(Dim:Parameter{Timestart,TimeEnd},Measure:Measure)
2.Fill in Timestart and TimeEnd
3.Use this MDXQuery to create MDXSubset
4.Finally , you can use cell to control this Dynamic subsets


MDXQuery:
{
StrToMember("[Time].[" +[Parameter].([Parameter].[Timestart],[Measure].[String])+"]")
:
StrToMember("[Time].[" +[Parameter].([Parameter].[TimeEnd],[Measure].[String])+"]")
}
Rtel
Posts: 59
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Dynamic subsets on Time dimension

Post by Rtel »

Thank you Win for the suggestions. It helps me improve of solution designs. I will try some of those methods

Thank you Howard, I have generalized your MDX statement. I hope i understood them correctly.
------------------------------------------------------------------------------------------
Hardcoding element name
{
strToMember('[Dimension1].[element_1]')
:
strToMember('[Dimension1].[element_N]')
}

-------------------------------------------------------------------------------------------
Reading values from a cube
{
strToMember('[Dimension1].[' +
[Cube_A].[Dimension_A].[element_1] , [Cube_A].[Dimension_B].[element_1]
+]')
:
strToMember('[Dimension1].[' +
[Cube_A].[Dimension_A].[element_1] , [Cube_A].[Dimension_B].[element_2]
+]')
}
---------------------------------------------------------------------------------------------

Question: If I want to use a variable (named svElement_1 and svElement_N) in Ti process, can I write as follows ?
subsetcreatebyMDX ( Mysubset,
{
strToMember('[Dimension1].[' +
svElement1
+]')
:
strToMember('[Dimension1].[' +
svElement_N
+ ]')
}
)

---------------------------------------------------------------------------------------------

Thank you !
Wim Gielis
MVP
Posts: 3105
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: Dynamic subsets on Time dimension

Post by Wim Gielis »

subsetcreatebyMDX ( Mysubset,
‘{
strToMember([Dimension1].[' |
svElement1
| ‘])
:
strToMember([Dimension1].[' |
svElement_N
| ‘])
}’
);
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
Rtel
Posts: 59
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Dynamic subsets on Time dimension

Post by Rtel »

Thank you Wim !
Post Reply