Hi, All
I am good with establishing name range for TM1ELLIST function with subset.
Ref: https://www.ibm.com/support/knowledgece ... llist.html
I faced challenge while establishing name range for the TM1ELLIST function through MDX.
my syntax : ( I MUST BE DOING MISTAKE WITH EXCEL SYNTAX CONSTRUCTION)
FYI : MDX syntax works perfectly in the subset expression window
construction1:
=TM1ELLIST("NewWorld:ConversionMap_Obj",,,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) = Sheet1!$B$3 and [ConversionMap].([ConversionMap_Msr].[NewDivision]) =Sheet1!$B$5 )}",,)
Construction2:
=TM1ELLIST("NewWorld:ConversionMap_Obj","Default","","",0,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) = "30003" and [ConversionMap].([ConversionMap_Msr].[NewDivision]) = "300081")}","","")
Can anyone through the light on the issue?
Thank You
Dharav
TM1ELLIST with MDX
-
- MVP
- Posts: 3120
- 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: TM1ELLIST with MDX
First off, Sheet1!$B$3 needs to be taken I’m out of the literal string enclosed by double quotes.
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
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: TM1ELLIST with MDX
@wim:
Removed Double Quotes:
Instead of reference to Sheet1!$b3$ , i put the value 30003
=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) =30003 and [ConversionMap].([ConversionMap_Msr].[NewDivision]) =300081)}",,)
I am still not able to get it right. Could you please help me to understand the concept to structure the formula?
Thank You
Dharav
Removed Double Quotes:
Instead of reference to Sheet1!$b3$ , i put the value 30003
=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) =30003 and [ConversionMap].([ConversionMap_Msr].[NewDivision]) =300081)}",,)
I am still not able to get it right. Could you please help me to understand the concept to structure the formula?
Thank You
Dharav
-
- MVP
- Posts: 3120
- 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: TM1ELLIST with MDX
Hello,
I haven’t used TM1ELLIST yet so I cannot help you in detail with this one.
I haven’t used TM1ELLIST yet so I cannot help you in detail with this one.
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
-
- Posts: 72
- Joined: Wed Aug 15, 2018 3:18 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: TM1ELLIST with MDX
Hi, All & Wim
i figured it out.
Cause was the construction of the MDX for the worksheet.
While referencing the cell from worksheet to MDX statement, we need to utilize
"&CHAR(34)&$b$5&CHAR(34)&" instead of just referencing cell as $b$5.
=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) ="&CHAR(34)&$b$5&CHAR(34)&")}")
Although, following mdx statement is not working
=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) ="&CHAR(34)&$b$5&CHAR(34)&" and [ConversionMap].([ConversionMap_Msr].[NewDivision]) ="&CHAR(34)&$b$8&CHAR(34)&")}")
do we need to use any character with "and" in the MDX for worksheet?
Thank You
Dharav
i figured it out.
Cause was the construction of the MDX for the worksheet.
While referencing the cell from worksheet to MDX statement, we need to utilize
"&CHAR(34)&$b$5&CHAR(34)&" instead of just referencing cell as $b$5.
=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) ="&CHAR(34)&$b$5&CHAR(34)&")}")
Although, following mdx statement is not working
=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) ="&CHAR(34)&$b$5&CHAR(34)&" and [ConversionMap].([ConversionMap_Msr].[NewDivision]) ="&CHAR(34)&$b$8&CHAR(34)&")}")
do we need to use any character with "and" in the MDX for worksheet?
Thank You
Dharav
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: TM1ELLIST with MDX
Can you use single quotes to represent string in MDX inside double quotes?