Dear all,
I will be happy if you can share your thoughts and ideas on the below issue that I am experiencing.
I have a visualization where I am calculating Sequential Growth, which as you most probably know is the current quarter - the previous quarter / previous quarter. This is the formula that I use:
(sum({<NumericQuarter={'$(=max(NumericQuarter))'}>} Units)
- sum({<NumericQuarter={'$(=max(NumericQuarter)-7)'}>} Units))
/ sum({<NumericQuarter={'$(=max(NumericQuarter)-7)'}>} Units)
In other words:
20201 - 20194 / 20194
Quarter field is in the following format - 2019Q4
NumericQuarter field is in the following format - 20194
Right now, this calculation is working as the latest quarter that I have available is 2020Q1. So basically the result from the above formula is: 20201 - 7 = 20194
The problem happens when I reload new data. Lets say that on the next reload of the data the latest that I will have is going to be 2020Q2 and this case the formula will not work because it will need to be max quarter - 1, instead of - 7.
I will need to manually edit the formula to be:
(sum({<NumericQuarter={'$(=max(NumericQuarter))'}>} Units)
- sum({<NumericQuarter={'$(=max(NumericQuarter)-1)'}>} Units))
/ sum({<NumericQuarter={'$(=max(NumericQuarter)-1)'}>} Units)
In order to get 20202 - 20201 / 20201
Do you have any ideas how I can do the above without having to edit my formula every time the data changes? I know I can achieve that with offsets but I am pretty new into the offset part of qliksense, so I would be happy to get some assistance...
Let me know if something is unclear and thanks in advance!
