6 Replies Latest reply: May 14, 2017 11:47 PM by Robin Hausdörfer RSS

    T12 Calculaton / Trailing 12 Month Average in Qlik Sense

    Benson Lin

      I am trying to figure out how to use Qlik Sense to do a  T12 calculation

      Trying to write the express in Qlik to go from the data set (on the left) to the T12 average per month (on the right)

      In Excel, the T12 formula is effectively sum(Month1:Month12)/12.

       

      Any suggestions?

       

               

      DATA SETTrailing 12 Months (T12)
      MonthProduct Sales $ Qty MonthProduct T12 Avg $ T12 Avg Qty
      1/31/2016TV $ 525,000          1,500 12/31/2016TV $ 1,556,041.67                    4,446
      2/29/2016TV $        1,225,000          3,500 1/31/2017TV $  1,614,375.00                    4,613
      3/31/2016TV $            595,000          1,700 2/28/2017TV $  1,585,208.33                    4,529
      4/30/2016TV $            700,000          2,000 3/31/2017TV $  1,666,875.00                    4,763
      5/31/2016TV $        1,750,000          5,000 4/30/2017TV $  1,739,791.67                    4,971
      6/30/2016TV $        1,050,000          3,000 5/31/2017TV $  1,696,041.67                    4,846
      7/31/2016TV $        1,575,000          4,500 12/31/2016Monitor $      555,833.33                    5,558
      8/31/2016TV $        2,625,000          7,500 1/31/2017Monitor $      568,333.33                    5,683
      9/30/2016TV $        2,975,000          8,500 2/28/2017Monitor $      560,000.00                    5,600
      10/31/2016TV $        2,852,500          8,150 3/31/2017Monitor $      542,500.00                    5,425
      11/30/2016TV $        1,225,000          3,500 4/30/2017Monitor $      578,333.33                    5,783
      12/31/2016TV $        1,575,000          4,500 5/31/2017Monitor $      563,750.00                    5,638
      1/31/2017TV $        1,225,000          3,500
      2/28/2017TV $            875,000          2,500
      3/31/2017TV $        1,575,000          4,500
      4/30/2017TV $        1,575,000          4,500
      5/30/2017TV $        1,225,000          3,500
      1/31/2016Monitor $            200,000          2,000
      2/29/2016Monitor $            350,000          3,500
      3/31/2016Monitor $            410,000          4,100
      4/30/2016Monitor $            320,000          3,200
      5/31/2016Monitor $            675,000          6,750
      6/30/2016Monitor $            850,000          8,500
      7/31/2016Monitor $            950,000          9,500
      8/31/2016Monitor $            750,000          7,500
      9/30/2016Monitor $            350,000          3,500
      10/31/2016Monitor $            970,000          9,700
      11/30/2016Monitor $            505,000          5,050
      12/31/2016Monitor $            340,000          3,400
      1/31/2017Monitor $            350,000          3,500
      2/28/2017Monitor $            250,000          2,500
      3/31/2017Monitor $            200,000          2,000
      4/30/2017Monitor $            750,000          7,500
      5/30/2017Monitor $            500,000          5,000