Calculation for Sequential Growth

[ShellyG]

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!