TM1 MDX filter level by attribute and retain parent

Post Reply
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

TM1 MDX filter level by attribute and retain parent

Post by BigG » Thu Jan 20, 2011 12:47 am

Hi,

I have a dynamic subset with MDX expression

Code: Select all

{FILTER
( {Descendants
({ [Employee].[Total Employee]} )}, 
[Employee].[Existing]= "Y")}
This is ok for returning Existing employees (flagged Y in attribute 'Existing'). Issue is I am attempting to bring back the parent 'Total Employee' too, but as soon as I filter by 'Y' the 'Total Employee' does not have attribute flagged so is removed.

I cannot flag 'Total Employee' as Y as this will cause the same issue with another subset for new employees (which filters by '')

Anyone have a solution to this? I am stuck.

Thanks in advance

G
GG

stormrider
Posts: 5
Joined: Tue Nov 18, 2008 5:40 am

Re: TM1 MDX filter level by attribute and retain parent

Post by stormrider » Thu Jan 20, 2011 12:56 am

Try a Union

e.g.

{Union(
{FILTER
( {Descendants
({ [Employee].[Total Employee]} )},
[Employee].[Existing]= "Y")},
{ [Employee].[Total Employee]}
)}

BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: TM1 MDX filter level by attribute and retain parent

Post by BigG » Thu Jan 20, 2011 4:05 am

great, thanks this worked and I chucked in HIERARCHIZE so it would sort better.

Code: Select all

{ HIERARCHIZE( {Union(
{FILTER
( {Descendants
({ [Employee].[Total Employee]} )}, 
[Employee].[Existing]= "Y")},
{ [Employee].[Total Employee]}
)}
 ) }
GG

jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: TM1 MDX filter level by attribute and retain parent

Post by jstrygner » Thu Feb 10, 2011 11:29 am

You would probably get the same effect without HIERARCHIZE by adding the consolidation in the first part of union:

Code: Select all

{Union(
  {[Employee].[Total Employee]}
  ,
  {FILTER({Descendants({ [Employee].[Total Employee]})},[Employee].[Existing]= "Y")}
)}

lotsaram
MVP
Posts: 3317
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 MDX filter level by attribute and retain parent

Post by lotsaram » Thu Feb 10, 2011 12:00 pm

For something simple like this you can also just use the "+" operator to join sets rather than using UNION. (Used to be important in the old days of 256 character limits for MDX strings, less so now)

Code: Select all

{
  {[Employee].[Total Employee]}
   + 
  {FILTER({Descendants({ [Employee].[Total Employee]})},[Employee].[Existing]= "Y")}
}

jyoung66
Posts: 24
Joined: Fri Aug 13, 2010 2:14 am
OLAP Product: TM1
Version: PAW 2 PAX
Excel Version: 2016

Re: TM1 MDX filter level by attribute and retain parent

Post by jyoung66 » Sat May 17, 2014 8:37 am

Hi and thanks for the excellent post here, it has helped me greatly

What if I JUST want the filtered parent and not the decendants?

My example below is based on what has gone before, and to get the just total I filter by level, but I have hard coded this (the 5 below)

To get what I want, I would need an MDX way of determining the max level, can you help me?


{TM1FILTERBYLEVEL(
{Union(
{[Business_Unit].[All_Business_Unit]}
,
{FILTER({Descendants({ [Business_Unit].[All_Business_Unit]})},[Business_Unit].[PropertyManager]="Jimi Hendrix")}
)}
,5
)}

jyoung66
Posts: 24
Joined: Fri Aug 13, 2010 2:14 am
OLAP Product: TM1
Version: PAW 2 PAX
Excel Version: 2016

Re: TM1 MDX filter level by attribute and retain parent

Post by jyoung66 » Sat May 17, 2014 8:51 am

Never mind, solved my own problem...Its simple really:

{TM1FILTERBYPATTERN(
{Union(
{[Business_Unit].[All_Business_Unit]}
,
{FILTER({Descendants({ [Business_Unit].[All_Business_Unit]})},[Business_Unit].[PropertyManager]="Jimi Hendrix")}
)}
,
"All_Business_Unit"
)}

jyoung66
Posts: 24
Joined: Fri Aug 13, 2010 2:14 am
OLAP Product: TM1
Version: PAW 2 PAX
Excel Version: 2016

Re: TM1 MDX filter level by attribute and retain parent

Post by jyoung66 » Sat May 17, 2014 9:40 am

Ooops

spoke too soon I'm afraid
Here is the modified query, which now just looks at Total level and N levels

{Union(
{[Business_Unit].[All_Business_Unit]}
,
{TM1FILTERBYLEVEL(
{FILTER({Descendants({ [Business_Unit].[All_Business_Unit]})},[Business_Unit].[PropertyManager]="Jimi Hendrix")}
,0
)}
)}

And here is the output from a view that has nested Region and Business Unit on rows:
+ QLD + All_Business_Unit 3,611,664.50
+ QLD 7420 188,370.98
+ QLD 8064 112,490.48
+ QLD 10233 95,354.71
+ QLD 10238 -1,500.00
+ QLD 10243 196,988.08
+ QLD 10266 73,647.77
+ QLD 10268 146,821.05
+ QLD 16539 148,067.04

2 things are wrong here:
1. All the n levels summed give 960,240.11, but All_Business_Unit = 3,611,664.50 , I need it be the sum 960,240.11
2. I need no other regions to show, although I have hard coded 1 region here (QLD), IF I had other regions(i.e NSW VIC etc) then they would also be returned even though they do not relate to "Jimi Hendrix" in my example

so close, yet so far away.

tomok
MVP
Posts: 2631
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: TM1 MDX filter level by attribute and retain parent

Post by tomok » Sat May 17, 2014 2:41 pm

1. All the n levels summed give 960,240.11, but All_Business_Unit = 3,611,664.50 , I need it be the sum 960,240.11
You're expecting MDX to function in a way that it cannot. MDX can only filter which elements to display, it cannot change the definition of the elements. If the node All_Business_Unit includes all the business units in the dimension, then any cube view showing that element will always include the sum of all the business units, regardless of which of those children elements you choose to display underneath it. The only way to do what you want to do is to create an alternate hierarchy in the dimension, which cannot be done via MDX.
2. I need no other regions to show, although I have hard coded 1 region here (QLD), IF I had other regions(i.e NSW VIC etc) then they would also be returned even though they do not relate to "Jimi Hendrix" in my example
I believe the EXCEPT expression is what you need here.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Post Reply