Page 1 of 1

MDX Filtering on StrMember

Posted: Tue Nov 19, 2019 12:37 pm
by tomok
I’m looking for a complete MDX solution for a filtered subset based on an attribute, with the kicker that the attribute is of an element represented by a string value in a cube. I have a time dimension, called Periods-Months, and in that dimension I have an attribute called “TM1 Last Day Serial Date”, which holds a numeric value. I also have a control cube, called “Global Variables”, that holds a string value of the last month where Actuals have been loaded. Let’s say this value is “Oct-2019”. I know I can create an MDX subset containing all the months in the future that looks like this:

Code: Select all

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= 21853)
What I am looking for is to replace the 21853 with the name of the element which holds the 21853 as an attribute. That element can be found in the Global Variables cube. I know I can get that element by:

Code: Select all

{StrToMember("[Periods-Months].[" + [Global Variables].([Global Values].[Value],[Global Variables].[LACT]) + "]" )}
What I can’t figure out is how to marry these two MDX’s together.

Re: MDX Filtering on StrMember

Posted: Tue Nov 19, 2019 1:02 pm
by Mark RMBC
Hi Tomok,

it would be easier if the global value was numeric, but how about:

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= strtovalue([Global Variables].([Global Values].[Value],[Global Variables].[LACT])))

regards, mark

Re: MDX Filtering on StrMember

Posted: Tue Nov 19, 2019 1:10 pm
by tomok
Mark RMBC wrote: Tue Nov 19, 2019 1:02 pm Hi Tomok,

it would be easier if the global value was numeric, but how about:

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= strtovalue([Global Variables].([Global Values].[Value],[Global Variables].[LACT])))

regards, mark
Thanks for the input but that doesn't work because LACT is a string representing the name of an element. What I need is the value in an attribute of the element represented by LACT.

Re: MDX Filtering on StrMember

Posted: Tue Nov 19, 2019 1:49 pm
by Mark RMBC
Sorry misread that! You want to get the attribute value for Oct-2019!

How about?

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= StrToMember("[Periods-Months].[" +[Global Variables].([Global Values].[Value],[Global Variables].[LACT])+ "]" ).properties("TM1 Last Day Serial Date") )

Re: MDX Filtering on StrMember

Posted: Tue Nov 19, 2019 2:35 pm
by tomok
Mark RMBC wrote: Tue Nov 19, 2019 1:49 pm Sorry misread that! You want to get the attribute value for Oct-2019!

How about?

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= StrToMember("[Periods-Months].[" +[Global Variables].([Global Values].[Value],[Global Variables].[LACT])+ "]" ).properties("TM1 Last Day Serial Date") )
Thanks, that was close and helped me get to the correct syntax, which is:

Code: Select all

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= 
StrToValue(StrToMember("[Periods-Months].[" + [Global Variables].([Global Values].[Value],[Global Variables].[LACT]) + "]" ).Properties("TM1 Last Day Serial Date")))