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.
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.
Because of the l1 reference this will remain on year level regardless how the hierarchy is expanded.