If you have a multi-level hierarchy in TARGIT, you can refer to levels with modifiers that can help you refer to:

- Children (the level below the current one)
- Siblings (all dimension values one the same level as the current one)
- Levels (level 0 being the all level of a dimension, level 1 being the top of the hierarchy, level 2 next level etc)

However, it is difficult in certain scenario to identify which level you are currently addressing.

Let's say you want to create a column that does different calculations on different levels of the hierarchy.

Then you would like to write something like:

**if level1 then calculation1 else if level2 then calculation2 else if level3 then calculation3 etc...**

That's not possible out of the box, but here's a few intermediate calculations that allows you to do just that.

Here's a crosstab showing a hierarchy with 4 levels: Terrritory, Country, Region and Customer:

Now we start from the bottom to identify levels.

1st calculation (as a new measure) uses this syntax to identify lowest level (level 4 in this case) of the hierarchy:**if allcount(d1, all(c), m1) = 0 then 4 else 0**

If a row has no "children", we are at the lowest level of the hierarchy, and we will mark these up with the value 4.

Now we have a new measure (m2). We can use this to move up to next level of the hierarchy with this calculation (as a new measure):

**if max(d1, all(c), m2) = 4 and min(d1, all(c), m2) = 4 then 3 else 0**

This does a test on measure 2 (m2) - which is my previous calculation and say if both min and max are 4 - I'm pretty sure that we are looking at the rows identified in the previous calculation.

If these rows are the children of the current level - then that level must be the one above level 4.

Result:

Same method applied on the next level with this syntax:

**if max(d1, all(c), m3) = 3 and min(d1, all(c), m3) = 3 then 2 else 0**

And one more time with this calculation:

**if max(d1, all(c), m4) = 2 and min(d1, all(c), m4) = 2 then 1 else 0**

Now we have this intermediate result:

Our final calculation just sums op measures 2 to 5 (our 4 calculations):

**sum(all, 0, m2:m5)**

Now we are there:

All this was just prerequisites to be able to write syntax like this:

**if sum(d1,0,m6)= 1 then calculation1 else if sum(d1,0,m6)=2 then calculation2 else if sum(d1,0,m6)=3 then calculation3 else calculation4**

Now you have the levels ready for your if sentence :-)

**NOTE**: This tip works when your hierarchy is fully expanded. With a partially expanded hierarchy, you will get results that might not be what you expected.

## Comments

Louise Stub HansenHi Niels

Thanks for the trick.

It works perfectly when I have expanded the hierarchy:

But when I collapse the hierarchy or use the filter, the first levels disappear.

Is it possible to insert a condition to test how many levels it open? Fx a formel that tests the level of the first row in the hierarchy (the value of "Niv" in the first row)? To modify the number of levels.

Louise Stub HansenGot it - Just added +1-sum(all,0l(1),m2:m5) to the calculation :)

Please sign in to leave a comment.