I learned much of this from a post by Comment:
For starters, use this calculation on an actual date to record the week instead of two fields, one for the week and one for the year:
DateField - DayOfWeek(DateFIeld) + 1
This will take any date and return the date of the first day of that week (Sunday). This approach will allow your calcs to refer to weeks from the previous year.
IF the above calculation is a field called cWeek,
cWeek-21 will return the Sunday date 3 weeks earlier.
Let's call the field c4Week. Now you can make a second table occurrence of Weekly_Sales and link them as:
Weekly_Sales::c4Week < Weekly_Sales 2::cWeek AND
Weekly_Sales::cWeek > Weekly_Sales 2::cWeek
Now a calculation, Average ( Weekly_Sales 2::Units Sold) should give you the 4 week average.
Thanks for getting back to me. Everything works great for one product being sold so when I implemented this for multiple products I came up with two issues.
First because I have different products sold so I had my UPC in the Products table related to the Weekly_Sales table. When I did the calculation for the Average it would give me the Average for all the units sold for every product. I need the average for each individual product. I then I tried to relate the UPC from Products to Weekly_Sales 2 which gave me an Average for each product but all the information from related from the Products table do not show up.
Second issue I came across is that if I did the Average calculation from Weekly_Sales 2 in the Products table it would give me the average of all the weeks.
I'm sure somehow the two issues are somehow related.
You should start by finding Weekly_Sales records in the last 4 weeks. Then sort them by UPC, and use a summary field in a sub-summary by UPC part to show the average figure (you don't need a body part for this report).
Note that the report is produced from the Weekly_Sales table.
Cause the data I was importing was from our retailer, the weeks given were based on the retailer's dates. The data given was as follows: 201011 - which meant year 2010 week 11. I figured out a way to calculate 4 weeks ago date based on a 52 week calendar (e.g. if the date was 201003 the 4 weeks ago return should be 200951).
cWeek = 201004
c4Week = If ( (cWeek / 100) - .04 ≤ Floor ( cWeek/100 ) ; cWeek - 52 ; cWeek - 4 )
then the return should be
c4Week = 200952
Then you can follow the averaging function Phil has shown. Hope this helps anyone else with this problem.
The data given was as follows: 201011 - which meant year 2010 week 11.
That's not sufficient. There are several methods to number weeks; some assign up to 54 weeks to a year, some 53. You need to find out the exact method being used. One thing is certain: no method that has only 52 weeks in a year can last for long.