I have been requested to develop an specific calculation and i am having a very hard time wrapping my head around the best way to do that on Qlik Sense.
I have a very large dataset with the following format:
Table 1:
WEEK
STORE_NAME
PRODUCT
SALES
STOCK
202001
A
Z
10
60
202001
B
Z
3
30
202002
A
X
5
70
202002
C
Y
2
20
202002
D
X
1
5
202003
A
Z
3
40
202003
B
X
2
30
202003
C
Y
10
60
The fields are fairly self-describing. It is important to notice that the "stock" column contains the total stock of that stock at a given week. Hence, it doesn't make sense to sum stock across weeks.
Now i wish to build a table showing the "Weeks of stock" for the latest week for each store.
"Weeks of stock" is known by calculating the stock of a given week, divided by the average sales in the past 4 weeks. To execute that I've built the following formula:
Using the Store dimension and the above formula as measure, I've built the following table with the following format and correct values.:
Table 2:
STORE
LATEST_WEEKS_OF_STOCK
A
10
B
5
C
3
D
2
Now to my actual problem:
I now wish to add the 4 weeks moving average and 50 weeks moving average to table 2. I imagine i would have to build some kind of aggregation for that and use the RANGEAVG formula. But to be honest, i am completely lost on how build this formula on top of the formula i originally built.
The desired output would look like this:
STORE
LATEST_WEEKS_OF_STOCK
LATEST_4MA_WEEKS_OF_STOCK
LATEST_50MA_WEEKS_OF_STOCK
A
10
?
?
B
5
?
?
C
3
?
?
D
2
?
?
What is a good formula to use for the LATEST_4MA_WEEKS_OF_STOCK and LATEST_50MA_WEEKS_OF_STOCK?
