Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Wim,
Please go through the attached document, hope this helps you.
Regards,
Jagan.
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