TM1 Forum

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

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.

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])+"]")
}

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 !

subsetcreatebyMDX ( Mysubset,
‘{
strToMember([Dimension1].[' |
svElement1
| ‘])
:
strToMember([Dimension1].[' |
svElement_N
| ‘])
}’
);

Thank you Wim !