Rolling sum of sales over previous weeks

[#1]

Hi All,

For some days now the problem below is my top priority. However I seem to be unable to solve it. Maybe someone here can help:

Please consider a database table with salesrecords.

Fields: Year, Week, Salesvalue

There is around 6 years of sales available.

I'm looking for a solution in which I can make a rolling 12 week total. For example:

2010 - 47 - 600

2010 - 48 - 500

2010 - 49 - 550

2010 - 50 - 400

2010 - 51 - 350

2010 - 52 - 400

2011 - 1 - 100

2011 - 2 - 150

2011 - 3 - 125

2011 - 4 - 130

2011 - 5 - 75

2011 - 6 - 700

2011 - 7 - 775

Feelds needed: This week; Last week; Last 4 weeks; Last 12 weeks

Selecting Year 2011, Week 7 Should result in:

Year	Week	This Week	Last Week	Last 4 weeks	Last 12 weeks
2011	7	775	700	775+700+75+130	775+700+75+...+550+500

Selecting Year 2011, Week 6 Should result in:

Year	Week	This Week	Last Week	Last 4 weeks	Last 12 weeks
2011	6	700	75	700+75+130+125	700+75+130+...+500+600

Fields this week and last week I resolved myself. For this I used set analysis. The 2 last fields `last 4 weeks` and `last 12 weeks` are causing me a headache especially when taking into account the change of years.

I tried working with a week counter; but this solution is not acceptable to management as they just want to select year and week.

Can anyone help?

Thanks and regards,

Wim

[jagan]

Hi Wim,

Please go through the attached document, hope this helps you.

Regards,

Jagan.

[#3]

Thanks Jagan,

I tried your solution and in the provided scenario it works.

However when implemented with real data I do of course have dimensions such as Customer, Product, etc. In this implementation the total running 12 weeks for each customer is the same as the total for all customers. Adding to the variable `customer.name=` only helps when an actual customer is selected and thus no cross customer comparison is possible. Is there a work around for this so that it works flexible with dimensions?

Thanks and regards,

Wim