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

# 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:
%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?

• ###### Re: Last twelve months balance figure

You need to create a rolling period to get this. Please find attached the modified file. Check the load script for the additional script I have added for rolling periods.

Regards,

Sajeevan

• ###### Last twelve months balance figure

I was hoping to solve it without any script additions, but your solution does work.

Thanks!

• ###### Last twelve months balance figure

If the provided solution has resolved the issue, can you mark this post as correct answer?

Regards,

Sajeevan