MDX Filtering on StrMember

[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.

[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

[tomok]

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.

[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") )

[tomok]

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")))