What you describe sounds possible. But I think you left out a few details. Until I got to this sentence:
I'd essentially need to have each graph entry be the average (or sum) of all sales that fall between two rolling dates.
I didn't even know you wanted to chart the data. Since charting an average for the most recent 52 weeks of data would only produce a single data point on your chart, there appear to be some details missing. Are you charting averages for different products or groups of products? Is each point on the chart a different average for a different 52 week interval?
Phil, do you ever leave these boards? Thankfully it seems the answer is no :-)
Let's just simplify and say I want total sales (all products) for the last 52-weeks on a weekly basis (week 1 this year would include all sales starting week 2 last year, week 2 this year will have total sales since week 3 last year etc etc). Thus, one point per week (but each point shares data with another 102 points, 51 on each side of it). Does that make sense?
I'm not sure I follow that. See if this agrees with what you have in mind:
point 1: total for 52 weeks of data starting with week 3 data of this year but including data from all data from the preceding 52 weeks
point 2: total for 52 weeks but starting with week 3
Point 3: same typ total but from week 4
If so then it would make sense to set up a table of one record per week that you link to your related table of data.
You could, for example, define cWeek in your current data table as a calculation field that returns the date for Sunday of the same week: DateField - DayOfweek ( DateField ) + 1
Then, if you create a record for each week that you want to chart with a date for that same Sunday date, you can set up a calculation field that stores 52 Sunday Dates in a field to form a Return Separated list of dates. There are custom functions that can produce that list or you can use a looping script.
Then this relationship:
WeeksTable::cSundayList = YourDataTable::cWeek
will match to all records for that same week plus the preceeding 51 weeks. Summary fields defined in YourDataTable can return totals, averages, etc based on that 52 week time period for charting on a chart set up on the WeeksTable.
Ah, the list ... that was the "missing link". I have year-week right now but would just change that to a serial number. Then, as you say, I can easily produce a list field with 52 entries separated by comma (or whatever). Will your relationship return any entry where one of the list items in cSundayList matches cWeek?
The list must have values separated by a return. It won't work with a comma delimiter.
Will your relationship return any entry where one of the list items in cSundayList matches cWeek?