2 Replies Latest reply: Dec 6, 2011 10:10 AM by wimz2685 RSS

    Rolling sum of sales over previous weeks

      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:

      YearWeekThis WeekLast WeekLast 4 weeksLast 12 weeks
      20117775700775+700+75+130775+700+75+...+550+500

      Selecting Year 2011, Week 6 Should result in:

      YearWeekThis WeekLast WeekLast 4 weeksLast 12 weeks
      2011670075700+75+130+125700+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