MDX Issue

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

MDX Issue

Post by Analytics123 » Wed Feb 14, 2018 6:01 pm

Hi ,

I am using the below mdx on an insert subset to test the results .

{Filter({TM1FILTERBYLEVEL({DESCENDANTS([Customers SoldTo Only].[Region - 001]) }, 0)},([AR History].[AR Measures].[Recent Activity],[AR History].[Months].[Feb 2018])=0)}

I want to check the value of Recent activity for all the n level customer under Region 001 for Feb 2018 .

But I am getting an error like

Syntax error at or near '[AR History].[Months].[Feb 2018])=0)}', character position 132 .

Can the filter condition be on 2 dimension values , ([AR History].[AR Measures].[Recent Activity],[AR History].[Months].[Feb 2018])=0)

Should I use something like tuple in cognos .


Any help is appreciated . If that works I will alos need to know count the accounts .

Thanks,

Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: MDX Issue

Post by Analytics123 » Wed Feb 14, 2018 6:15 pm

Also I tried this with just one dimension on filter condition .


{Count(Filter({TM1FILTERBYLEVEL({DESCENDANTS([Customers SoldTo Only].[Region - 001 - CROSSROADS - CROSS040]) }, 0)},([AR History].[AR Measures].[Recent Activity]])=0))}


But does count makes logical to have here ? Will it give the count of the returned values ?


Thanks,

User avatar
gtonkin
MVP
Posts: 602
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Issue

Post by gtonkin » Wed Feb 14, 2018 6:38 pm

Try something like:

Code: Select all

{Filter(
{TM1FILTERBYLEVEL({DESCENDANTS([Customers SoldTo Only].[Region - 001]) }, 0)},
[AR History].([AR Measures].[Recent Activity],[Months].[Feb 2018])=0
)}
The cube name should be after the tuple/set then the area defined within the brackets. I split the code for readability.

User avatar
tomok
MVP
Posts: 2464
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 Issue

Post by tomok » Wed Feb 14, 2018 6:44 pm

Have you read the MDX Primer? You should keep a shortcut to it and refer often. Check your syntax for filtering on cube values and your mistake will pop out at you.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: MDX Issue

Post by Analytics123 » Wed Feb 14, 2018 8:04 pm

Can I have a Count surrounding the above expression .

Post Reply