Number of levels above an element

Post Reply
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Number of levels above an element

Post by Hector2005 »

Hello there, hope all of you is okay.
I have a little question.

If I have this hierarchy
-Corporate
China
+America
+Japan
-India
+Exploration
-Development
X


Is there a way through it I can know number of levels above any leaf element for example "X" number of levels or the last ancestor level is 3 for "china" the last ancestor level is 1.

Best Regards.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Number of levels above an element

Post by David Usherwood »

TM1 supports the (rarely used) ASCENDANTS function which could help. Alternatively write a recursive TI - but remember that elements can have multiple parents.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Number of levels above an element

Post by Mark RMBC »

In a simple dimension I think you could get away with the following. By Simple, I mean set number of levels but the leaf itself could be the immediate child of any level of parent)

Number of levels in dimension minus the level of the leafs immediate parent plus one (Worksheet code below)

Code: Select all

DNLEV(Dimension) - ELLEV(Dimension,ELPAR(Dimension,LeafElement,1)) +1
However as soon as you arrive at a ragged hierarchy plan B is required...I think!

cheers, Mark
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Number of levels above an element

Post by Wim Gielis »

David Usherwood wrote: Fri Apr 27, 2018 2:27 pm TM1 supports the (rarely used) ASCENDANTS function which could help. Alternatively write a recursive TI - but remember that elements can have multiple parents.
Hello David,

You are right about multiple parents. Here is an example.

If I manually drill up on the level 0 element until I get to the top of the dimension in a rather complicated dimension, I get e.g.:
07.png
07.png (6.66 KiB) Viewed 3493 times
If I use the MDX, I get:
08.png
08.png (4.06 KiB) Viewed 3493 times
So it depends on the hierarchy in which the element is located, for which elements you will get back from the statement.

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Hector2005
Posts: 42
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Number of levels above an element

Post by Hector2005 »

Thanks a lot for your concern, Sorry about my latency but I think I will search on another way to get a solution.

Best Regards,
Post Reply