Complex Aggregations - Looking for Help on Rolling Periods

[xcelguy]

I am working on a dashboard for a large retailer. This retailer has seasonal spikes and is looking for the ability to smooth out the bumps in their trend lines. My idea is for any individual month to look at that month and the X preceeding months and average the numbers over that time period. I've been trying to figure out how to sum X months of data under a single month.



For x=2, and the month of march 2013 this would mean, march 2013, february 2013, and January 2013 would all be summed together under the month of March. I am guessing I will need the help of some referential table that shows these one to many relationships but I am not sure how I would need to structure the query to get this to work.

I do not want to use excel to plot this as I would need to to plot 24 months of data to get rolling data for a 12 month period. Please advise if you have any knowledge of how I can accomplish this task.

[June7]

Do you want cumulative year-to-date?

Or quarterly averages?

[xcelguy]

I dont really want either of those. Quarterly is close but I do not want defined periods, i want rolling periods. Basically i am trying to get the month i am looking at to have the sum of that month's numbers and however many proceeding months I want to include. So if my rolling period is 12 months, and I am looking at October 2013, this line would actually aggregate the sales / return units from November 2012-October 2013. Once I aggregate the values i can divide them to get a 12 month rolling average. The next month it would be December 2012 - November 2013 and them January 2013 - December 2013. Basically a month rolls in as a month rolls out of the 12 month window and the last month in that window is the plot date.

[NTC]

I believe what you want to review is the DateAdd method....Bing that...

also basic query criteria can give you what you term as 'rolling' in the sense that one can set up to be:
>Now()-365

how your data is structured and where in the form, query/record set or report you need to implement things can affect the precise way the syntax and such but hopefully this gets you going forward a little.......

[June7]

I think NTC's suggestion is what you are looking for. Just one issue with using Now() or Date(). What if you run the report on Jan 5 but you really want the data through Dec 31? Need to input the desired through date as a dynamic parameter.