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.
Number of levels above an element
-
- Posts: 42
- Joined: Wed Jan 31, 2018 2:01 pm
- OLAP Product: MOLAP
- Version: 10.2
- Excel Version: 2013
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: Number of levels above an element
TM1 supports the (rarely used) ASCENDANTS function which could help. Alternatively write a recursive TI - but remember that elements can have multiple parents.
-
- 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
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
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
cheers, Mark
-
- MVP
- Posts: 3117
- 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
Hello David,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.
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
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
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
-
- 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
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,
Best Regards,