Addressing levels in hierarchies - 1

When you work with hierarchies, you often need to address a certain level of the hierarchy and that can be a little difficult.

Let me start by showing you a few examples:

In this crosstab I would like to create a calculate the difference between the 2 year in the filter.

mceclip0.png

As you can see – it’s a hierarchy of time that we are looking at on the horizontal axis.
That hierarchy is currently expanded to the quarter level – but I guess the users might expand it further (to month/day level).

However the hierarchy is expanded, I want to stick with the year level.

My time hierarchy has 4 levels: Year, Quarter, Month, Day

In terms of TARGIT syntax we can address:

  • Level 0 (means the grand total of the time dimension – in this case the sum of 2019 and 2020)
  • Level 1 (means year level – in this case either the sum of 2019 and the sum of 2020)
  • Level 2 (means month level – in this case either the sum of Q1, Q2, Q3 and Q4)
  • Level 3 (means day level) – in this case a particular day in a particular month)

This formula (Year growth)

Sum(d2(l1),0,m1)-sum(d1(l1),0,m1) means subtract 2019 from 2020 on year level.

mceclip1.png

Because of the l1 reference this will remain on year level regardless how the hierarchy is expanded.

Was this article helpful?
1 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.