1 2 Previous Next 23 Replies Latest reply on Jul 25, 2011 4:35 PM by nkolios

    Rolling average over multiple records

    nkolios

      Title

      Rolling average over multiple records

      Post

      Hi,

      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.

      Niko 

      all.jpg

        • 1. Re: Rolling average over multiple records
          philmodjunk

          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.

          http://en.wikipedia.org/wiki/Moving_average

          • 2. Re: Rolling average over multiple records
            nkolios

            Hi I am trying to get a 12 week rolling average for the amount of hours a member of staff works in a week.  

            • 3. Re: Rolling average over multiple records
              philmodjunk

              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.

              • 4. Re: Rolling average over multiple records
                nkolios

                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.

                Thanks again

                • 5. Re: Rolling average over multiple records
                  philmodjunk

                  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:

                  Sum (TimeSheets::Hours)/12

                  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.

                  • 6. Re: Rolling average over multiple records
                    nkolios

                    Hi I put a reply on technet as I can upload some snapshots.  I think I am missing something!

                    • 7. Re: Rolling average over multiple records
                      philmodjunk

                      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.

                      • 8. Re: Rolling average over multiple records
                        nkolios

                        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. 

                        • 9. Re: Rolling average over multiple records
                          philmodjunk

                          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?

                          • 10. Re: Rolling average over multiple records
                            nkolios

                            Hi I don;t have a file sharing site.  Have you tried refreshing, it seems to work ok for me.

                            • 11. Re: Rolling average over multiple records
                              philmodjunk

                              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.

                              • 12. Re: Rolling average over multiple records
                                nkolios

                                thats really strange, is there an email address I can send it too.  I have iDisk, could I use that?  

                                • 14. Re: Rolling average over multiple records
                                  philmodjunk

                                  You did miss something and it's pretty subtle:

                                  Change your c12WeekAve expression to read:

                                  Sum ( TimeSheets12Wks::Weekly_Hours ) / 12

                                  1 2 Previous Next