12 Replies Latest reply on Jan 13, 2017 7:55 AM by philmodjunk

    Sick time calculation issue

    mobara

      Per a new Massachusetts sick time law, we need to accrue sick time at a rate of 1 hour sick time for every 30 hours worked up to a maximum of 40 accrued sick time hours per year. The calculation field below handles the accrual just fine, but can't seem to stop the accrual once the total accrual reaches the 40 hour mark.

       

      (Sick Time 2016 Carry Over +  Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334

       

      This field creates the per shift accrual based on the various payroll fields.

      Does anyone have any ideas on how to stop this accrual once the YTD total reaches 40 Hours of accrual?

        • 1. Re: Sick time calculation issue
          schamblee

          Use an if statement

           

          if((Sick Time 2016 Carry Over +  Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334 > 40 ; 40 ; (Sick Time 2016 Carry Over +  Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334)

           

          If the calculation is over 40 then amount default to 40 else it the result of your calculation.

          • 2. Re: Sick time calculation issue
            siplus

            Let (

             

            calc = yourcalc;

             

            Min(calc; 40)

             

            )

            • 3. Re: Sick time calculation issue
              essam

              as sipuls said ..

              use Let statement to make the calc more easier.

              • 4. Re: Sick time calculation issue
                mobara

                Thank you for your response. I tried it, and it only works on a single entry. If there are multiple entries it still accrues more than the 40 hours on a YTD basis. Assuming that there are 52 weekly pay periods of 40 hours, the results need to stop accruing once the 40 hours of accrual has been reached on a YTD basis.

                 

                Thanks again!

                • 5. Re: Sick time calculation issue
                  siplus

                  in that case you need a self relationship gathering data from related records, I guess.

                   

                  Relating on financial year and employer ID.

                  • 6. Re: Sick time calculation issue
                    mobara

                    I am not familiar with the "Let" statement or the required syntax. Can you elaborate or send me an example that I can follow?

                     

                    Thank you!

                    • 7. Re: Sick time calculation issue
                      keywords

                      Let ( ) allows you to simplify complex calculations and in the process make their operation more efficient. A good example is the If ( ) calc suggested by schamblee; in that calc, you would have FM adding up all those fields to see if they come to more than 40, then if they don't, adding them all up again just to post the answer. Using Let ( ), however, you can save the result of the initial adding up as a variable and then just use that answer if the If test fails.

                      Essentially, the Let ( ) function has two parts. In the first part you declare whatever values you want to use as variables, separated by semi-colons, with the complete set enclosed in square brackets. In the second part you post the result you want the calculation to deliver, which could be just one of the variables if you wish. Generically it looks like this:

                      Let (

                      [

                        firstData = <name of a field from the database, for example>

                      ; secondData = <maybe another field>

                      ; today = <some other piece of data you want to use, for example Get ( CurrentDate )>

                      ; otherDate = <maybe another date>

                      ; result = <the calc to deliver the result required: e.g. If ( today – otherDate > 7 ; firstData ; secondData )

                      ] ;

                      result

                      )

                       

                      So in your case, it might be:

                      Let (

                      [

                        sickHours = (Sick Time 2016 Carry Over +  Facility Wkday Hrs + Fac Hol Wday OT Hrs + Homecare Wkday Hrs + Home Hol Wday OT Hr + Office Staff Regular Hours + Other Hours + Visits Wkday Hours + Visits Wkend Hours + # of Travel Hrs + # of Travel OT HOL Hrs + Office Staff Regular OT Hours) * .03334

                      ; max = 40

                      ; result =  If ( sickHours > max ; max ; sickHours )

                      ] ;

                      result

                      )

                      • 8. Re: Sick time calculation issue
                        mobara

                        Thank you for your reply. The let statement did the same as the if statement. It stops any 1 entry from exceeding the 40 hours, but not multiple entries, of from a YTD basis. Is there a way to have the calculation field do a YTD calc and bring back results to apply to the max?

                        • 9. Re: Sick time calculation issue
                          siplus

                          If I understand correctly, you have this calc in a shift record.

                          Also if I understand it correctly, you are allowed to accumulate 40 hrs. per year, which don't get blasted on january 1st.

                           

                          I think that you should

                           

                          1) leave out the Carry over from last year

                          2) just do the calc in a sickHours Field

                          3) have a sickHoursTotal which in a let ()

                            - sums the sick hours of every shift of the current year via a relationship in sh;

                            - returns as result the max(40; sh)

                          4) have a cumulatedSickHoursTotal which is = sickHoursTotal + carry over of previous years.

                          • 10. Re: Sick time calculation issue
                            keywords

                            Your own sick hours calculation adds up no fewer than 12 separate elements. Examine that.

                            • 11. Re: Sick time calculation issue
                              mobara

                              Yes you are correct on both counts. We are a home healthcare agency employing primarily per diem staff that in many cases do not work enough to accrue 40 hours of sick time in an entire year. For that reason, it does not make sense to blast them 40 hours. The other part of the law says, whatever portion of sick time accrued, and not used, can be carried over into the new year up to a max of 40 hours. So to account for the carry over, I create 1 record on 01/01/xxxx with the amount of the carry over, so in essence the carry over field is just one of the many fields/records that the calc is  accounting for. Due to the regulations imposed on us, we are required to breakdown #'s of hours & payroll the we do in various settings which is why the calc fields are many. That is the background to this.

                               

                              I have never worked with Let statements before so I do not know how to structure the statement for it to work.

                              • 12. Re: Sick time calculation issue
                                philmodjunk

                                Seems to me that the whole problem might be handled more simply and with much greater flexibility if you did not set up separate fields for each type of "hours" that need to be part of this calculation. A related table with a field for the employee ID, a field that identifies the category (2016 carryover, Facility Wkday....), A date field and a field for the Hours worked.

                                 

                                Then either a summary field or an aggregate function (sum ( ) ) can be used with a relationship or Execute SQL query to get the total hours for an employee.

                                 

                                I see this as more flexible as changes to what categories of hours need be tracked becomes a data entry change rather than changing multiple field definitions (and this calculation here) is avoided.