Page 1 of 1
Ascendants vs Parent/Ancestor/Ancestors
Posted: Thu Aug 26, 2021 7:59 pm
by 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
)
)}
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 8:12 am
by 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.
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 8:51 am
by 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
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 9:59 am
by 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.
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 10:12 am
by Wim Gielis
What did you try as expressions ?
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 3:35 pm
by 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))
)}
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 4:17 pm
by 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))}
regards,
Mark
Re: Ascendants vs Parent/Ancestor/Ancestors
Posted: Fri Aug 27, 2021 4:54 pm
by 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