TM1 Forum

Hey guys!

Excel 2007
Tm1 9.4.1 FP003.

I am trying to use the function ELPARN in a rule to determine the number of parents an element has. Well strangely enough it returns 1 for all elements except the highest level elements which it returns 0(which is correct).

Has anyone seen this before? I have worked with ELPAR before and sometimes noticed it doesn't give me the correct answers. Is it just me or has anyone else noticed these functions not working properly all the time?


Cheerio!

par3 wrote: Excel 2007
Tm1 9.4.1 FP003.

I am trying to use the function ELPARN in a rule to determine the number of parents an element has. Well strangely enough it returns 1 for all elements except the highest level elements which it returns 0(which is correct).

Has anyone seen this before? I have worked with ELPAR before and sometimes noticed it doesn't give me the correct answers. Is it just me or has anyone else noticed these functions not working properly all the time?

I know that this is an obvious question, but just for certainty's sake... is it possible that the elements do have only one parent? (Bearing in mind that ElParN returns the number of immediate parents of an element only, not the total number of ancestors which may be more than one level above it.)

The only way that you'll get a number >1 is in a dimension with multiple hierarchies.

Allan,

I have to differ with you there. This is the explanation form the reference guide.

"ELPARN returns the number of parents of an element in a specified dimension. "

"ELPARN('Model','Wagon 4WD')

In the Model dimension, the element Wagon 4WD is a child of both Total Wagons and Total 4WD. Therefore, both Total Wagons and Total 4WD are parents of Wagon 4WD. The function returns 2."


Now unless Total Wagons and Total 4WD are to different roll-ups within the same dimension I understand.

Anyway if this is the case my rule won't work. I need to determine the highest level parent for an element. Any idea how I can do that?

Hi,

ELPARN returns the total number of direct parents an element has. Most likely these parents will be in different (parallel) hierarchies, but it is possible tht these are in a single hierarchy (but this is not really relevant in relation to what the function does).
There is no rules function that determines the highest level ancestor of an element, but you maybe could do a check the other way around and use the ELISANC function to check whether an element is somewhere underneath a node in the hierarchy.

Michel

Thanks Michel.

Can't really use ELISANC because I first need to determine what the highest level parent is before I can test if the element is a child of it.

par3 wrote:I have to differ with you there. This is the explanation form the reference guide.

To be honest I do not understand where you see a difference between reference guide and Alans explanation.

par3 wrote:what the highest level parent is before I can test if the element is a child of it.

I think it will become easier if we use the same terminology. Otherwise it gets people confused. Following TM1 logic I suggest using:
- parent (for immediate parents only),
- child (for immediate children only),
- ancestor (both for immediate parents and parents of their parents and all the way to the top (root) element),
- descendant (both for immediate children and children of their children and all the way to level 0 elements).

Coming back to your problem:

The way I understand from your posts, you are trying to count a number of ancestors for an element you write a rule for. You have only one root element in your dimension (you wrote only one element returns 0 for ELPARN) but there are of course many elements, some of them are more deep in the hierarchy, some more shallow. What you also have is every element (except root one) has only one parent, so only one number of ancestors.

If that is the real situation:
1. I am affraid there is no nice way to do it via rules, so consider if TI is not an option.
2. If not, you can try to use the ELPAR as many times as many levels your dimension has (in your case dimension has as many levels as the single root element's level + 1) and combining it with IF function and checking, if the parent name equals root's name - yes, this will look terrible, especially when you have many levels, but it may do the work (it would be nice if you knew the potentially maximum level your dimension may ever have in the future).

HTH