Ascendants vs Parent/Ancestor/Ancestors

[Keith Would]

Hello

I managed to get this MDX to do what I want (for use in a PAfE report), which is filtering on the period number, then generating the parents and sorting by hierarchy.

It seems a bit odd to generate all Ascendants then, filter some of them out again.
I have tried using Parent/Ancestor/Ancestors but cannot get any of them to work in this way.
Could anyone help with the syntax for using them?

Thanks
Keith

Code: Select all

{HIERARCHIZE(					
TM1FILTERBYLEVEL(					
GENERATE(					
					
FILTER(					
{TM1SUBSETALL ( [Year historic data] ) },					
([Year historic data].[Period number])>					
84					
)					
					
,ASCENDANTS([Year historic data].CurrentMember)					
),0,1					
)					
)}

[Wim Gielis]

Hello,

I see a number of hits for these on my MDX page: https://www.wimgielis.com/tm1_mdxstatements_EN.htm
You could have a look there and experiment.

[MarenC]

Hi,

Haven't you solved your own problem here? Or are you simply looking for something more elegant?

If you are looking for a more elegant solution then I don't think it exists. For example, I think the following may give you the same result:

Code: Select all

{ Hierarchize(TM1ROLLUP(
 {FILTER( {TM1SUBSETALL( [Year historic data] )}, [Year historic data].[Period number] > 84)},
 { FILTER( {TM1SUBSETALL( [Year historic data] )}, [Year historic data].[Period number] > 84)  })) +
 FILTER( {TM1SUBSETALL( [Year historic data] )}, [Year historic data].[Period number] > 84) }

But I certainly wouldn't call it more elegant!

Maren

[Keith Would]

Indeed. I have solved it but thought any of those 3 would be more elegant...but couldn't get them to work so I thought I'd ask.

[Wim Gielis]

What did you try as expressions ?

[Keith Would]

This gives me all Ancestors, but not the leaves:

Code: Select all

{HIERARCHIZE(	
	
GENERATE(	
	
FILTER(	
{TM1SUBSETALL ( [Year historic data] ) },	
([Year historic data].[Period number])>	
84	
)	
	
,[Year historic data].CurrentMember.ANCESTORS)	
	
)}	


[b]This returns nothing:[/b]
{HIERARCHIZE(

GENERATE(

FILTER(
{TM1SUBSETALL ( [Year historic data] ) },
([Year historic data].[Period number])>
84
)

,[Year historic data].CurrentMember.PARENT)

)}


[b]This generates the leaves only:[/b]
{HIERARCHIZE(

GENERATE(

FILTER(
{TM1SUBSETALL ( [Year historic data] ) },
([Year historic data].[Period number])>
84
)

,ANCESTORS([Year historic data].CurrentMember,0))

)}


[b]This generates the Ancestors at level 1 only:[/b]
{HIERARCHIZE(

GENERATE(

FILTER(
{TM1SUBSETALL ( [Year historic data] ) },
([Year historic data].[Period number])>
84
)

,ANCESTORS([Year historic data].CurrentMember,1))

)}

I can't knit the last 2 together

[Mark RMBC]

Hi Keith,

The last one can be knitted together as follows:

Code: Select all

{HIERARCHIZE(GENERATE(FILTER({TM1SUBSETALL ( [Year historic data] ) }, ([Year historic data].[Period Number])>
84),{Ancestors([Year historic data].CurrentMember,1),Ancestors([Year historic data].CurrentMember,0)}))}

You need the Ancestors in curly braces.

You also need curly braces to get the .parent to work, so as follows:

Code: Select all

{HIERARCHIZE(GENERATE(FILTER({TM1SUBSETALL ( [Year historic data] ) }, ([Year historic data].[Period Number])>
84), {[Year historic data].CurrentMember.Parent}) + FILTER({TM1SUBSETALL ( [Year historic data] ) }, ([Year historic data].[Period Number])>
84))}

Though I cant see that these are any better that what you came up with!

regards,

Mark

[Keith Would]

Yes, they both have more characters than my original solution.

There is never the "perfect" solution. Good to know the syntax to get those to work though for future reference.

Thanks all