Hi all,
Relatively new to TM1 and also the forum (1st post). Apologies in advance for my newbie type question.
I'm trying to create a filter based dynamic subset that includes all level 0 elements except for elements that begin with "02"
Any suggestions for writing that into the expression window are appreciated!
Question for filter based subset
-
- MVP
- Posts: 2831
- 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: Question for filter based subset
If you plan on creating MDX-based subsets you should definitely read the MDX Primer http://www.bihints.com/except_and_valid ... ierarchies. EXCEPT is the function you're after.
-
- Posts: 2
- Joined: Tue Jul 25, 2017 5:48 pm
- OLAP Product: Cognos TM1
- Version: 10.2.2
- Excel Version: Excel 2016
Re: Question for filter based subset
Hi Tom,
Thanks for the reference. I was able to figure it out using the exception function.
{EXCEPT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [DIM1] )}, 0)}, {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [DIM1] )}, 0)}, "02*")})}
Thanks for the reference. I was able to figure it out using the exception function.
{EXCEPT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [DIM1] )}, 0)}, {TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [DIM1] )}, 0)}, "02*")})}
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Question for filter based subset
Hello
I would also use this expression. The alternative with the Instr function I find less interesting.
I would also use this expression. The alternative with the Instr function I find less interesting.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly