Hi,
I've been using Qlik for the past year but i still have a long way to go.
Currently i want to calculate a moving average, on script, but at the same time i want to ignore some of the data.
My data has the following columns: ID, Store, SALESDATE, PromoFlag, PromoCode and QTD.
From here, I want to get the average sales for the combination of product/store of the previous n-periods (on my example i'm using 56 days or 8 weeks). I want to store the data in the column AvgComQTD/Day.
At the same time, whenever "PromoFlag" is 0 (meaning Promo Code is not null), I don't want to include the sales on the calculation and I want to take the value of the previous day where "PromoFlag" is 1.
| ID | Store | SALESDATE | PromoFlag | Promo Code | QTD | ComQTD(last 56days) | ComCountDate(last 56) | AvgComQTD/Day |
| 972710 | 651 | 12/01/2016 | 1 | - | 10,00 | 10,00 | 1,00 | 10,00 |
| 972710 | 651 | 20/01/2016 | 1 | - | 30,00 | 40,00 | 2,00 | 20,00 |
| 972710 | 651 | 28/01/2016 | 0 | PPD0010 | 290,00 | 40,00 | 2,00 | 20,00 |
In the end i want to have the following columns on my table: ID, Store, SALESDATE, PromoFlag, PromoCode, QTD and AvgComQTD/Day.
Once i have the table loaded I want to make some analysis like:
| PromoCode | Min of SALESDATE | Max of SALESDATE | Sum of QTD | Sum of Avg. QTD/Day | Incremental | Incremental(%) |
| PPD0010 | 28/01/2016 | 28/01/2016 | 290 | 20,00 | 270,00 | 1350% |
| PPD0031 | 26/02/2016 | 26/02/2016 | 120 | 0,00 | 120,00 | |
| PPD0042 | 22/03/2016 | 25/03/2016 | 130 | 130,00 | 0,00 | 0% |
| PPD0066 | 30/04/2016 | 30/04/2016 | 140 | 20,00 | 120,00 | 600% |
Ideally I would like to have the calculation on script, but if this can be achieved with aggr() function I can also give it a try.
Please find attached an excel file with the sample data and the view i want to achieve.
Thank you,
Michael
