I have a table that stores daily prices and I would now like to do calculate the 2-day average and show the weekly variances in a query.
To explain, the query needs to:
1. automatically pull the Wednesdays from the Pricing table
2. calculate the average price of the product based on the prices over the previous 2 days (i.e. the Monday and the Tuesday)
2. calculate the variance in these averages on a weekly basis (based on Wednesday's date).
I have attached a file in MS Excel that shows the calculations for the month of August 2012.
Hope that you can help!