Hi,
I'm trying to get a forecast accuracy/error report working in Qlik Sense. What I'm trying to do is to calculate MAPE, mean absolute percent error, for each part no in the attached data, for each site.
The data includes part no (sales part), family, group, category, site sold on, forecast for the period and actual (demand) for the period, as well as forecast error, absolut error, error percent and absolute percent error.
What I want to do is calculate MAPE for each part no, on each site, for each period for the previous 6 periods.
Ex:
| Period | t | Forecast (F) | Actual (A) | Error | Absolute percent error (APE) |Ft-At|/At |
|---|
| April | 6 | 1100 | 1000 | 100 | 10% |
| May | 5 | 1500 | 1100 | 400 | 36,4% |
| June | 4 | 1050 | 1400 | -350 | 24% |
| July | 3 | 1250 | 950 | 300 | 31,6% |
| August | 2 | 1100 | 1350 | -250 | 18,5% |
| September | 1 | 1000 | 1250 | -250 | 20% |
MAPE = Sum(APE)/n = 1/n * SUM(from t=1 to n=6) |Ft-At|/At = (10+36,4+24+31,6+18,5+20)/6 = 23,4%
So for my data, part no 1111 would for January 2017 and America have a MAPE of 51,5% (quite awful actually)
Since I will use the MAPE in a few other calculations in charts later, I'd like this calculation to happen in the data load editor and, using Excel terms, be added as an extra column for each row. (if there isn't 6 months before, the MAPE should be 0)
I was thinking of some kind of for loop, but couldn't really figure out how it should be done. I have been looking around a bit on the forum, but haven't found anything that fits my need.
Any and all help appreciated!
Thanks
Tobias
