9 Replies Latest reply on Feb 28, 2012 10:26 AM by jason.delooze

    Calculation help

      I'm hoping someone can help me out with a calculation that is way beyond my ability. I'm only self taught on FileMaker (databases in general) since last October. The calculation I need done seems pretty tricky to me.


      I'm working on a sick report db that will automatically calculate the number of working days someone has been absent due to being sick or injured. Our work schedule is five days on followed by three days off, repeat. Our work force is broken down into nine different squads; 1,2,3,4,5,6,7,8 and Admin, each representing a different schedule. For example:


      Squad 1 will work Jan 1,2,3,4,5 off Jan 6,7,8 back on Jan 9,10,11,12,13 off three more etc.

      Squad 2 will work Jan 2,3,4,5,6 off Jan 7,8,9 back on Jan 10,11,12,13,14 off three more.

      Squad 3 starts Jan 3 etc..I hope you can get the idea now.

      Admin works Monday thru Friday with every other Friday off.


      So if someone books off sick on Feb 11 2012 in squad 3 and returns to work on Apr 6 2012 still in squad 3 I would like it to calculate how many working days they have missed.


      I already have fields for "date_off", "squad_off", "date_return" "squad_return". Besides the field to do this calculation is there anything else I'll need. Any help with this would be greatly appreciated.




        • 1. Re: Calculation help

          Hi Gregg,


          Logically will a person take a sick day when not scheduled to work? my guess is no.

          In recording the dates a person is out sick where will that field be? what table? my guess is attendance table

          I see four tables to do this: 1) the person table - employee (with their personal info)   2) the attendance table (sickday date goes here)  3) the group table      and   3) the join table ( tells what person is in what group)

          If you want to see sick absences in a report and the sickdayout field is in the attendance table,  base the report on the attendance table, find all records falling inside your report period and add summary field to count the records in the report.


          Person =< attendance

          Group =< join table >= person


          Hope this Helps,


          • 2. Re: Calculation help

            While a lot would depend on how strict you want to be on your first day off (can it be a non-work day etc), your calculation would basically take the form of:


            Date of interest (first sick day etc) - Date of reference (first day of the first cycle for the group) + group (1-9)


            With this basic date calculation above, you can use the DIV and MOD functions to get the PERIOD of the date (the nth ineration of the 8 day cycle) and the day within that cycle


            DIV (firstSickDay - DateOfReference + group ; 8) // the cycle of the date

            MOD (firstSickDay - DateOfReference + group ; 8) // the day within the cycle


            From here you would need to decide if the MOD caclculation was >5 (an off day) etc if you desired to make it more robust


            Now you can use the difference between the cycle numbers for the two dates and the difference between the days within the cycle for the two dates to handle the actual sick days.





            • 3. Re: Calculation help

              Hi Gregg,


              I have 2 questions.


              1. I understand how the Squads rotations work - an 8 day period, but the Admin people seem to be on a 14 day rotation - work 5, off 2, work 4, off 3, repeat.  Is that correct?


              2. Since you have 2 parameters denoting Squad (squad_off and squad_return), are we to assume that while a person is out sick, their squad can change - hence their work and off days change?  How can we know when (what date) they switch squads if the only 2 dates are date_off and date_return?


              I'll post a solution (custom function) next.



              1 of 1 people found this helpful
              • 4. Re: Calculation help

                Hi Gregg,


                Here's a custom function that computes the Missed Work Days for Squads 1-8 or Admin (Squad 0) from date_off to date_return.  I'll post the CF here and include a link to a demo file. Squad Missed Work Days.fp7




                // MissedWorkDays( date_off ; date_return ; squad )


                // squad = 0 is for Admin;

                // squad = 1 to 8 are for squads.




                   IsValid( GetAsDate( date_off ) ) and not IsEmpty( date_off ) and

                   IsValid( GetAsDate( date_return ) ) and not IsEmpty( date_return ) and

                   IsValid( GetAsNumber( squad ) ) and not IsEmpty( squad ) and GetAsNumber( squad ) >= 0 and GetAsNumber( squad ) <= 8 ;





                      sd = GetAsDate( date_off ) ;

                      ed = GetAsDate( date_return ) ;

                      squadOffset = GetAsNumber( squad ) - 1 ;



                      baseDate = Date( 1 ; 1 ; 2012 ) ;

                      workArray = Case( squadOffset < 0 ; "01111100111100" ; "11111000" ) ; // Distinguish between Squads and Admin

                      workDaysInPeriod = PatternCount( workArray ; "1" ) ;

                      lenWorkArray = Length( workArray ) ;



                      deltaDays = ed - sd ;

                      wholePeriods = Div( deltaDays ; lenWorkArray ) ;

                      partialPeriod = Mod( deltaDays ; lenWorkArray ) ;

                      leftShift = Mod( sd - baseDate ; lenWorkArray ) ;



                      crewArray = Case( squadOffset < 0 ; "01111100111100" ; Right( workArray ; squadOffset ) & Left( workArray ; lenWorkArray - squadOffset ) ) ;

                      partialCrewArray = Left( Right( crewArray ; lenWorkArray - leftShift ) & Left( crewArray ; leftShift ) ; partialPeriod )

                      ] ;





                      workDaysInPeriod * wholePeriods + PatternCount( partialCrewArray ; "1" )

                   ) ;





                • 5. Re: Calculation help

                  Thanks for the responses guys, sorry for the late reply, away for the weekend.


                  To answer your first question Jason you are correct about the Admin schedule, 5 on 2 off, 4 on 3 off, repeat. The reason for the squad_return is because it is possible for someone to return and be assigned to a different squad for various reason ( length of absence, time of year assignment changes).


                  To answer the question about people booking off, someone would only be booking off of work on a work day. And it needs to be accurate because once someone is absent for 20 consecutive days they get transferred.


                  I really appreciate the help, I go back to work tomorrow and will try to implement this, bear with me though, since I don't have a lot of experience I'll probably need to ask a few more questions to get this going. Thanks again.



                  • 6. Re: Calculation help

                    Hi Gregg,


                    First of all, the Custom Function I wrote does not care if the "date_off" or "date_return" are workdays or off-days; it will correctly count the missed workdays from the "date_off" up to but not including the "date_return" (because if they returned on that date, they weren't 'sick' that day).


                    As I said before, the "Admin" Squad is Squad 0 (zero) in the Custom Function - although the "squad" parameter in the custom function could be changed to a text-type field and handle "Admin" and Squad numbers "1", "2", ... , "8" as valid inputs.


                    To further explain my question regarding the "squad_return" field.  If someone gets sick while on one squad (squad_off) and is reassigned to another squad (squad_return) while hi is sick, we would need to know the date (date_switched) on which he was re-assigned to the new squad in order to account for the missed work days for each squad.  However, 2 calls to the Custom Function, one with "date_off", "date_switched", "squad_off" and the other with "date_switched", "date_return", "squad_return" would give the correct total of missed work days.  On the other hand, if the person isn't re-assigned to the new squad until his return, then only 1 CF call is needed and the "squad_return" is not needed by the CF.



                    1 of 1 people found this helpful
                    • 7. Re: Calculation help

                      Fortunately, a squad change wouldn't occur until someone came back to work. I'll let you know how I make out with this tomorrow.

                      • 8. Re: Calculation help

                        Works like a charm! I just had to adjust the baseDate to sync up with our work calender (Jan 1st wasn't really squad 1's first day) and all is well, thank you very much.

                        • 9. Re: Calculation help

                          Good to hear.  Just make sure the baseDate also syncs with the 1st day of the Admin folks as well - or adjust the "01111100111100" Admin array to start on the base Date you are using.