In this example we are looking at Salespersons' performance from one year to another. The basics of the crosstab is a single measure, No of Sales (m1), Salespersons on the vertical axis and two years on the horizontal axis.
The list have been ranked and sorted according to their 2022 No of Sales.
However, we can also see previous year's rank - their 2021 rank. And as a consequence, we can furthermore calculate how they have moved up or down the ranking list from last year to this year.
The Rank calculation has been inserted as a new measure (m2) and has this syntax:
rankdesc(0, all, m1)
The column reference '0' makes sure that the ranking values are based on the columns (years) where each individual Rank measure has been inserted.
The Rank change calculation is inserted as a single column and has this syntax:
sum(d1, 0, m2) - sum(d2, 0, m2)
The colored arrows have been inserted as a Color and gauge agent, using the Icon ... Classic Arrow (2 states) on the Ranke change calculation.
The color agents have been modified:
- The Positive agent has been changed to: value > 0
- The Negative agent is unchanged.
- A third agent has been added and moved to the top: Using the 'White square' icon series and for 'All values'.
- All three agents are using the 'Right align in cell' option.
Please sign in to leave a comment.