Averaging Last 4 Weeks
Hi I'm pretty new to FM and wanted to know how to build a database properly in which I can import a weekly sales report and have a calculation for average units sold per week for the last 4 weeks.
I have two tables right now, one as the Product table with the UPC related to a Weekly_Sales table. As of now I'm using date codes by week (01-52) and the year as a separate field. I did this in order to find out sales trends by week. I also have a GLOBAL date code for the current date so it tags all the newly imported data as that week and year.
I'm having trouble finding the average for the last 4 weeks of units sold from the Weekly_Sales table automatically; especially if the its Week 01 Year 2010 I'm not sure how to get it to automatically calculate data from Week 49 Year 2009.
Let me know if I'm approaching this correctly and I'm open to other ways to approach this.