TM1 MDX filter level by attribute and retain parent

[BigG]

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

[stormrider]

Try a Union

e.g.

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

[BigG]

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]}
)}
 ) }

[jstrygner]

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]

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]

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]

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]

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]

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.