MDX Filtering on StrMember

Post Reply
tomok
MVP
Posts: 2808
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

MDX Filtering on StrMember

Post by tomok » Tue Nov 19, 2019 12:37 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Mark RMBC
Community Contributor
Posts: 247
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX Filtering on StrMember

Post by Mark RMBC » 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

tomok
MVP
Posts: 2808
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX Filtering on StrMember

Post by tomok » Tue Nov 19, 2019 1:10 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Mark RMBC
Community Contributor
Posts: 247
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX Filtering on StrMember

Post by Mark RMBC » 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") )

tomok
MVP
Posts: 2808
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX Filtering on StrMember

Post by tomok » Tue Nov 19, 2019 2:35 pm

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")))
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Post Reply