TM1 Forum

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.

TM1 supports the (rarely used) ASCENDANTS function which could help. Alternatively write a recursive TI - but remember that elements can have multiple parents.

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)
However as soon as you arrive at a ragged hierarchy plan B is required...I think!

cheers, Mark

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.:
If I use the MDX, I get:
So it depends on the hierarchy in which the element is located, for which elements you will get back from the statement.

Wim

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,