Of what value(s) do you want to compute a Moving (Rolling) average?
Over what interval?
There are other parameters you may need to specify depending on what you want.
Hi I am trying to get a 12 week rolling average for the amount of hours a member of staff works in a week.
Is there a separate time sheet record for each employee? If so, does each record have an EmployeeID number to link to specific employees?
Is WeekID a serial number such that if this week is 23, the 12 week average would be for week id numbers 10...22? (or 11...23 to include the current week?)
Or is WeekID actually the date for Sunday of each week?
I'm thinking in terms of a self join relationship on Timesheets that matches by Employee and a range of weekID values so that your moving average can be computed by this expression:
Average ( MovAvgTimeSheet::Hours )
How you set up the relationship and some calculation fields to use as keys in it will depend on the nature of your WeekID field and that will only work with separate TimeSheet Records for every employee.
I have a separate staff table, which contains info about past and present staff. Because the status of any member of staff can change from week to week I decided not to have a link between the staff table and the timesheet table. When a new timesheet is created i have a script that goes to the staff table and extracts all the staff that are currently employed and are employed on an hourly basis and then need to enter timesheets. The script then goes to the timesheet tab where there is a portal and creates a series of records relating to to the staff who need to enter timesheets. The actual staff ID's are not pulled across bu that is a relatively simple item to correct.
Your second question about the weekID, there is a week ID which is an auto enter number and a week number because after 52 weeks we have to go back to 1. but as both numbers exist the ID would be the best one to use. so basically a new record in the timesheet table is created for every week with a list of the employees who have worked and the hours they have worked.
I was looking along the lines of a self join relationship but wasn't quite sure how it would work. There is an other issue calculating the average as I think the general function ignores blanks and I need to include in the average all entries over 12 weeks irrespective of whether they have an entry or not. Ideally I was hoping to produce these results in a portal along with other historical data.
I have done a bit of C programming years ago, which I don't think helps me with Filemaker. I was thinking of copying the data from 12 weeks of tables into one table, doing the calcs and pulling the information I need but I'm hoping there is a much more elegant way of dealing with this.
Personally, I would go ahead an link your employee and timesheet tables. The reasons you give don't convince me that this should not be done. THere are a variety of methods you can use to restrict what portion of records and data in the employees data is available when making use of this relationship and it can simplify a number of structural issues with your database.
That's up to you, however as it's not required for this solution.
All you need is to get that EmployeeID number into the timesheet table. (We don't want to use employee names for this as names are not unique and peope change their names from time to time.)
WeekID as a serial number should be perfect as long as you never "skip" a week in the series. It's also possible to set up this relationships with date fields and a date that calculates the needed 12 week interval.
Select the TimeSheets table occurrence in Manage | Database | Relationships and click the button with two green plus signs to make a new occurrence of it. Double click it and change it's name to TimeSheets12Wks.
Now you can define a calculation field, c12WksAgo as WeekID-12 and cLastWeek as weekID - 1. (I'm assuming that your average is to exclude the current week. With a small adjustment in this, you can change this to include the current week.)
Your relationship would for your moving average would look like this:
TimeSheets::EmployeeID = TimeSheets12Wks::EmployeeID AND
TimeSheets::c12WksAgo < TimeSheets12Wks::WeekID AND
TimeSheets::cLastWeek > TimeSheets12Wks::WeekID
Then, since you want to include blank values as 0 in the average, use this expression to compute the 12 week average:
Define a calculation field in TimeSheets with this expression.
To see these values in a portal is possible, but you haven't indicated exactly where you'd want that portal. I'd think you'd want to see that portal on an Employees based layout and that would definitely require you to add a relationship linking the employee table to your timesheet table.
Hi I put a reply on technet as I can upload some snapshots. I think I am missing something!
I'm not a member of TechNet.
If you want to include snapshots here you can edit your original post to replace the screen shot with a different one or you can upload the screen shots to a file sharing site and post the link to them here.
Hi, I have updated the post to include all 3 items I was trying to show. I have corrected the _kf_WeekID to a number, but it does not seem to have much effect. I have tried setting up a report and a portal based on the Timesheet12wks but I can't seem to get the expected numbers.
Unfortunately, I still see the original screen shot so I cannot see what you have done. Perhaps you can upload the image to a file sharing site?
Hi I don;t have a file sharing site. Have you tried refreshing, it seems to work ok for me.
Many filesharing sites are free.
I've refreshed several times both this time and the last time I accessed this thread and all I see is a screenshot of your relationships that shows payroll linked to Timesheets by a WeekID field.
thats really strange, is there an email address I can send it too. I have iDisk, could I use that?
You did miss something and it's pretty subtle:
Change your c12WeekAve expression to read:
Sum ( TimeSheets12Wks::Weekly_Hours ) / 12