7 Replies Latest reply: Aug 31, 2016 4:45 AM by Sunny Talwar RSS

    Accumulation rolling MQT with multiple dimensions

    Neal Bouwman

      Hi All,

       

      I have a question concerning calculating rolling MQT with multiple dimensions. I have this table with company sales per MonthYear. The Sales column is raw sales. And the Accumulated column is what I want. It adds the sales from the current MonthYear with the sales from the previous 2 MonthYears for that Company (first 2 months should not accumulate, because at least 3 months are needed). How to calculate this properly with an expression in QV (currently I calculated it by hand)?

       

      I checked this link: Calculating rolling n-period totals, averages or other aggregations, but it uses an example with 1 dimension only.

      I have tried checking 'Accumulation' with 3 steps back, but this does not seem to work unless I first make a selection of 1 company.

      I have tried different combinations of aggr(), rangesum(), above(), TOTAL, rowno(), etc. without success.

       

       

      MonthYear Company Sales Accumulated
      01/07/2014A100100
      01/07/2014B4545
      01/07/2014C3333
      01/08/2014A120120
      01/08/2014B5555
      01/08/2014C5050
      01/09/2014A150370
      01/09/2014B100200
      01/09/2014C80163
      01/10/2014A130400
      01/10/2014B95250
      01/10/2014C75205

       

      Hope some Qlik colleagues here have the golden tip!

       

      Many thanks