Running total based on weeks

[montubhardwaj]

I need the logic to create a complex report with running total. Your ideas/suggestions are welcome. I have the following fields:

Year: which has values as 2009, 2010, 2011

Week: for 2009-52 weeks, for 2010-52 weeks and for 2011- 11 weeks (completed so far)

Month: Jan-Dec

Sales

I want to create a report which will show the RUNNING total of sales based on weeks i.e. 1 week sales, 10 week sales, 35 week sales and 52 week sales.

Below is the example to better understand the situation supposing we are calculating 35 weeks sales:

For this year, we have completed 11 weeks, so the sales should be (sum of sales of 11 weeks from 2011) + (Sum of sales of 24 weeks from 2010)

In next week, when we will complete 12 Weeks, sales should be (sum of sales of 12 weeks from 2011)+ (Sum of sales of 23 weeks from 2010)

Supposing we are calculating 10 weeks sales:

For this year, we have completed 11 weeks, so the sales should be (sum of sales of 10 weeks from 2011)

Below is the mock-up of the report which is needed.

Please let me know your suggestions, ideas to achieve this.

Many thanks!