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:
=(SUM({<WEEK={"$(=MAX(WEEK))"}>}STOCK))/ (SUM({<WEEK={">=$(=MAX(WEEK,4))"}>}[SALES])/4)
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?
