I have table
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?
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.