1 Reply Latest reply: Mar 17, 2017 6:40 AM by Robert Svebeck RSS

    Forecast accuracy (MAPE)

    Tobias Gard

      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:

      PeriodtForecast (F)Actual (A)ErrorAbsolute percent error (APE) |Ft-At|/At
      April61100100010010%
      May51500110040036,4%
      June410501400-35024%
      July3125095030031,6%
      August211001350-25018,5%
      September110001250-25020%

       

      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

        • Re: Forecast accuracy (MAPE)
          Robert Svebeck

          Try something like this:

           

           

          Load
          [Part No],
          Site,
          Date,
          [Absolute forecast percent error],
          if( Previous(Previous(Previous(Previous(Previous([Part No] & Site)))))=[Part No] & Site,
          alt(
          (
          [Absolute forecast percent error]+
          Previous([Absolute forecast percent error])+
          Previous(Previous([Absolute forecast percent error]))+
          Previous(Previous(Previous([Absolute forecast percent error])))+
          Previous(Previous(Previous(Previous([Absolute forecast percent error]))))+
          Previous(Previous(Previous(Previous(Previous([Absolute forecast percent error])))))
          )
          /6
          ,0)

          ,0)
          as [MAPE]
          Resident "TABLE NAME OF YOUR FILE"
          order by [Part No], Site, Date; //MORE UNIQUE IDENTIFIERS?

           

           

          Regards, Robert

          Svebeck Consulting AB