visualizationqliksenseqlik-expression

Subtracting Current year Rank from Previous Year RANK in Qlik Sense

I want to show (current year rank - Previous year rank) in a dynamic way. That means if new data is loaded its automatically calculates the rank and displays.

Company                 RANK          Financial_Year
    A                    21             2021 
    A                    22             2022
    B                    46             2021
    B                    56             2021
    C                    78             2021
    C                    36             2022

Company                 RANK          
    A                    1                         
    B                    10           
    C                   -42
Solution

  • If you need to hard-code your fiscal years, you can use the following expression as a Measure in your table:

    Only({<Financial_Year={'2022'}>} RANK)
-
Only({<Financial_Year={'2021'}>} RANK)

    If you need for it to dynamically compare the most recent fiscal year to the previous fiscal year, you can do something like this:

    Only({<Financial_Year={'$(=Max(all Financial_Year))'}>} RANK)
-
Only({<Financial_Year={'$(=Max(all Financial_Year) - 1)'}>} RANK)

    This set expression works by using Dollar-sign expansion of numbers by using the $(=...) syntax.