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.
MDX filter based on element value
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: MDX filter based on element value
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.
Or if you don't have an attribute just convert the name to a number e.g.
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
)}
Code: Select all
{Filter(
{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)}
, StrToValue ( [cbm_year].currentmember.name )> 2012
)}
Declan Rodger
-
- Regular Participant
- Posts: 194
- Joined: Thu May 30, 2013 1:41 pm
- OLAP Product: Cognos
- Version: Planning Analytics 2.0.7
- Excel Version: 2010
Re: MDX filter based on element value
This is perfect! I didn't know about currentmember.name.
Thank you very much!
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.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)} ,[cbm_year].[myNumericAttribute] > 2012 )}
Code: Select all
{Filter( {TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER({[cbm_year].[All Years]}, ALL, RECURSIVE), 0)} , StrToValue ( [cbm_year].currentmember.name )> 2012 )}