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:
| Month | Sales past twelve months |
|---|
| 2011-02 | 14800 |
| 2011-03 | 15400 |
| 2011-04 | 15200 |
| 2011-05 | 15100 |
| 2011-06 | 14400 |
| 2011-07 | 14700 |
| 2011-08 | 15000 |
| 2011-09 | 15300 |
| 2011-10 | 15200 |
| 2011-11 | 15000 |
| 2011-12 | 14600 |
| 2012-01 | 14400 |
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?
