Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!