Rolling average over multiple records
I'm still a bit of a filemaker newbie, hence I'm a bit stuck. Basically I have a database for timesheet info. I have a table which represents basic details for each week, weekending date, week no etc. I think have 4 other tables joined through the week ID. One for timesheets, another for holidays, then sick leave and then maternity. The layout context is the first table containing the weekly info, I then have a set of tabs set up with a tab for each of the other tables with a portal for data entry.
When a new record for a new day is created the timesheet portal is populated with all the members of staff currently on the payroll and then we enter the time into each day.
What I am trying to achieve is a rolling 12 week average for each member of staff, it would also be nice to have this data on one of the portals.
I had thought of just copying the data into a temporary table and working out what I need to work out and then copying it back. But I would rather take advantage of using relationships if there was a way to do it.
Any help would be much appreciated.