Page 1 of 1
MDX filter based on element value
Posted: Wed May 30, 2018 12:24 pm
by kenship
Trying to perform MDX with filter without success.
I have a year dimension ("cbm_year"): From 1996 to 2050 all rolled up to "All Years".
The MDX needs to filter out all years before 2012. I thought it should be really simple:
{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
,[cbm_year] > 2012
)}
But I keep receiving a syntax error at or near "2012".
Appreciate any thoughts on this.
Thanks.
Re: MDX filter based on element value
Posted: Wed May 30, 2018 12:53 pm
by declanr
You have a couple of issues here:
1/ [cbm_year] in the filter is something that it doesn't know what to do with - you would need to specify that it is the name from the currentmember you are wanting to use.
2/ Even then you would be comparing string with a number
You could easily do it if you had a numeric attribute containing the year by replacing [cbm_year] with [cbm_year].[myNumericAttribute] e.g.
Code: Select all
{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
,[cbm_year].[myNumericAttribute] > 2012
)}
Or if you don't have an attribute just convert the name to a number e.g.
Code: Select all
{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
, StrToValue ( [cbm_year].currentmember.name )> 2012
)}
Re: MDX filter based on element value
Posted: Wed May 30, 2018 2:38 pm
by kenship
This is perfect! I didn't know about currentmember.name.
Thank you very much!
declanr wrote: ↑Wed May 30, 2018 12:53 pm
You have a couple of issues here:
1/ [cbm_year] in the filter is something that it doesn't know what to do with - you would need to specify that it is the name from the currentmember you are wanting to use.
2/ Even then you would be comparing string with a number
You could easily do it if you had a numeric attribute containing the year by replacing [cbm_year] with [cbm_year].[myNumericAttribute] e.g.
Code: Select all
{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
,[cbm_year].[myNumericAttribute] > 2012
)}
Or if you don't have an attribute just convert the name to a number e.g.
Code: Select all
{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
, StrToValue ( [cbm_year].currentmember.name )> 2012
)}