0

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
James Z
  • 12,209
  • 10
  • 24
  • 44
aarav
  • 5
  • 2

1 Answers1

1

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.

SmoothBrane
  • 721
  • 4
  • 5
  • Is 'all' Mandatory there ? – aarav Nov 23 '22 at 18:12
  • `All` is only mandatory if you want the expression to use the most recent fiscal year relative to the **entire data set** as opposed to seeing the most recent fiscal year only relative to the **current selections**. You can see more information about the `All` qualifier on [this Qlik Help page](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/define-aggregation-scope.htm). – SmoothBrane Nov 23 '22 at 19:11
  • Like if you select a **[Date]** of 2018-04-18 and the set expression uses the `All` qualifier (`=Max(all Financial_Year)`), it will still return 2022. However, if you select 2018-04-18 but you *don't* use the `All` qualifier (`=Max(Financial_Year)`), it will instead return 2018. – SmoothBrane Nov 23 '22 at 19:13
  • Thank you so much Brane for such a detailed explanation. Much appreciated :) Thank for helping out. – aarav Nov 24 '22 at 04:19
  • Happy to help! Make sure to mark the answer as being “correct” to help others find it in the future :) – SmoothBrane Nov 25 '22 at 14:43