Search code examples
expressiondata-analysisqliksense

Expression Multivariable CAGR in Qlik Sense

I have table

Years flag_new total_amt count_cust 2016 new 20000 1500 2016 exs 30000 1600 2017 new 18000 1400 2017 exs 60000 3100 2018 new 21000 2000 2018 exs 90000 4500 2019 new 20500 1500 2019 exs 130000 6500

I want to calculate CAGR of total_amt in Qlik Sense.

When I choose the filter pane two years, 2018 and 2016, then calculate ((60000+18000)/(30000+20000))^(1/(2018-2016))-1

When I choose only one year then set the lower year to 2016. For example: I choose in the filter pane 2019, then calculate CAGR 2016 to 2019.

And I want to able filter by flag_new too.

Can anyone show how to do the expression in Qlik Sense?

Solution

  • Loading the data in the data load editor:

    Data:
Load * 
Inline [
Years, flag_new, total_amt, count_cust
2016, new, 20000, 1500
2016, exs, 30000, 1600
2017, new, 18000, 1400
2017, exs, 60000, 3100
2018, new, 21000, 2000
2018, exs, 90000, 4500
2019, new, 20500, 1500
];

    And using this formula in the chart, e.g. KPI chart,..

    If(GetSelectedCount(Years) = 1,
Pow(Sum({<Years={"$(=Max(Years))"}>} total_amt)/Sum({<Years={"$(=Min(All Years))"}>} total_amt), 1/(Max(Years) - Min({<Years>} Years) + 1) - 1),
Pow(Sum({<Years={"$(=Max(Years))"}>} total_amt)/Sum({<Years={"$(=Min(Years))"}>} total_amt), 1/(Max(Years) - Min(Years) + 1) - 1))

    should work for you.

    It's a bit clunky due to the two different cases of always taking 2016 as Min(Years) when there is just one year selected, and taking Max and Min inside a selection when more than one year is selected.