3 Replies Latest reply: Mar 1, 2012 12:34 AM by Sajeevan Govindan RSS

    Last twelve months balance figure

      I am trying to show a table with sales figures per month of the last twelve months. Each row in the table shows the sales balance of the twelve past months from that month.

       

      The table is based on the following source data:

       

      Sales:
      LOAD * INLINE [
          %Month_key, #Sales
          201001, 1000
          201002, 1500
          201003, 900
          201004, 1300
          201005, 1100
          201006, 1600
          201007, 1000
          201008, 1200
          201009, 900
          201010, 1500
          201011, 1400
          201012, 1400
          201101, 1200
          201102, 1300
          201103, 1500
          201104, 1100
          201105, 1000
          201106, 900
          201107, 1300
          201108, 1500
          201109, 1200
          201110, 1400
          201111, 1200
          201112, 1000
          201201, 1000
          201202, 1300
          201203, 0
          201204, 0
          201205, 0
          201206, 0
          201207, 0
          201208, 0
          201209, 0
          201210, 0
          201211, 0
          201212, 0
      ];
      
      

       

      The table that I want to display looks like this:

       

      MonthSales past twelve months
      2011-0214800
      2011-0315400
      2011-0415200
      2011-0515100
      2011-0614400
      2011-0714700
      2011-0815000
      2011-0915300
      2011-1015200
      2011-1115000
      2011-1214600
      2012-0114400

       

      The sales figure from 2012-01 means that the sales from 2011-02 till 2012-01 are summed up: 1300 + 1500 + 1100 + 1000 + 900 + 1300 + 1500 + 1200 + 1400 + 1200 + 1000 + 1000 = 14400.

       

      The sales figure from 2012-01 means that the sales from 2011-01 till 2011-12 are summed up: 1200 + 1300 + 1500 + 1100 + 1000 + 900 + 1300 + 1500 + 1200 + 1400 + 1200 + 1000 = 14600.

       

      And so on, but only showing the last 12 months.

       

      I have tried to do this with the formula RANGESUM(ABOVE(SUM(#Sales), 0, 12)). But when I limit the dimension to the last twelve months this gives an incorrect result, because the above function looks at the table values and not at the source data.

       

      I have included a sample qvw file with my source data. Can anybody help me accomplish the desired result?