10 Replies Latest reply on Mar 4, 2014 1:40 PM by okachick@hotmail.com

    Holiday Statutory pay calculation

    okachick@hotmail.com

      Title

      Holiday Statutory pay calculation

      Post

           My calculation is not working for the holiday pay. What I am trying to do is take the employee total hours for a certain time period (find between two dates which will be four weeks of hours) and then calculate those hours ex: 188 hours and divide this by 20 days. It should come out as 9.40 which the employee will be paid for that stat holiday. However, it is calculating as .55.

           My calculation is "employee_total_hours/20

           Employee_total_hours is a calculating field.

           Would appreciate any help on this.

           Thank you

            

        • 1. Re: Holiday Statutory pay calculation
          philmodjunk

               And what is the calculation defined for Employee_Total_hours? It would appear from your result that it is not computing the correct value--apparently it is computing a total of 11 instead of 188.

          • 2. Re: Holiday Statutory pay calculation
            okachick@hotmail.com

                 The calculation is based on the following fields: (all number)

                 Hours worked + TravelTime+TonMile Hours+pre_post+pre_post TM hours=Employee _total_hours

            • 3. Re: Holiday Statutory pay calculation
              philmodjunk

                   Is this the exact expression you see in Specify Calculation?

                   Hours worked + TravelTime+TonMile Hours+pre_post+pre_post TM hours

                   Is this a calculation field or a number field with this expression set up as an auto-entered calculation?

                   And if you check the value in Employee _total_hours, is it computing the expected 188 hours? or do you see 11 when your calculation is returning .55?

              • 4. Re: Holiday Statutory pay calculation
                okachick@hotmail.com

                     Yes that is the exact calculation. All fields are number fields except for the Employee_Total_hours which is a calculation field showing (number) adding up all the other fields (as listed). Second, yes, it is calculating properly the hours ex: 188. It is not calculating the holiday pay however and I can't figure out why. It needs to calculate all hours within the date range when I do a find ex: 02-02-2014...03-02-2014 and then the hours showing within that find request, it calculated those hours by 20

                • 5. Re: Holiday Statutory pay calculation
                  philmodjunk
                       

                  It needs to calculate all hours within the date range when I do a find ex: 02-02-2014...03-02-2014 and then the hours showing within that find request, it calculated those hours by 20

                       There is nothing in these calculations that look at any data outside the immediate record. Nothing here will total up the hours over a found set of records. You'd need a summary field to get that total.

                  • 6. Re: Holiday Statutory pay calculation
                    okachick@hotmail.com

                         I have a summary field and it is still not calculating properly. Summary field is:

                         SumRegHours_TMHours which is based on the Total of Employe_Total_hours. Now it gives me 426.00 for each person even though their hours are all different? I changed the holiday Stat field for SumReghours_TMHours /20

                         What am I doing wrong here

                    • 7. Re: Holiday Statutory pay calculation
                      philmodjunk

                           And are you pulling up records for a single employee or a group of employees? A summary field will compute a value based on all records in your found set if you put it in the header, body or footer of your layout. If you put it in a sub summary layout part "when sorted by employee", you'll see a sub total for each employee.

                           When you refer to a summary field in a calculation, the summary field will return a value based once again on all the fields in your found set. To get the subtotal for an employee requires using the getSummary function and specifying the same "break" field as the field specified as the "sorted by" field for the sub summary layout part.

                           You may find this tutorial on summary reports helpful: Creating Filemaker Pro summary reports--Tutorial

                           Note: My post here assumes that the summary field is defined in the same table as that on which you have based your layout. How it evaluates follows yet another set of rules when the summary field is from a related table.

                      • 8. Re: Holiday Statutory pay calculation
                        okachick@hotmail.com

                             It is in a layout in the same table. The summary is from a group of records ex: 10 employees and a summary of all their daily hours within that found set. It is sorted according to what the sub summary layout part is (employeeid). I did not however use a getSummary function and not sure how to do that

                        • 9. Re: Holiday Statutory pay calculation
                          philmodjunk

                               getsummary is documented in FileMaker Help. Its syntax: GetSummary ( SummaryField ; BreakField )

                               IF your summary field is named SumReghours_TMHours and your sub summary layout part specifies "when sorted by EmployeeID", then the correct way to get the sub total shown in that sub summary layout part would be:

                               GetSummary ( SumReghours_TMHours ; EmployeeID )

                          • 10. Re: Holiday Statutory pay calculation
                            okachick@hotmail.com

                                 WooHoo! Worked like a charm. OMG I could of saved myself 6 hours work.

                                 Thank you so much!