6 Replies Latest reply on Oct 17, 2013 12:33 PM by klbloomfield

    Average Summary Field Isn't Working as Planned

    klbloomfield

      Title

      Average Summary Field Isn't Working as Planned

      Post

           We use FM Pro 11 and have a database for our payroll data.  We load the payroll data in and report it based on the Pay Period Date.  One of our layouts displays the total hours worked by employees.  Here is an example for one employee:

           Employee: Jane Doe

           Pay Period      Total Hours Worked

           1                       73.2

           2                       72.0

           3                       61.5

           4                       48.5

           5                       48.6

           6                       48.8

           Total Hours  352.6

           All of this data works correctly.  However, we tried to add a summary field to find the average hours worked for the displayed pay periods.  The average displayed in FM is 56.6 but we expect to see 352.6/6=58.8.  This one example is actually closer than some of the others.  Any idea what might be going wrong?  For the summary field, the weighted average box is not selected and the summarize repetitions box is 'All together'.  Thanks for your help.

        • 1. Re: Average Summary Field Isn't Working as Planned
          philmodjunk

               Does each row of data in your example represent a single record or a group of records. The average summary field will divide the total hours by by the total number of records with a value in the field being averages so if each of these rows represent a group of records, you may not get the result that you expected.

          • 2. Re: Average Summary Field Isn't Working as Planned
            klbloomfield

                 You are correct.  Each row is actually a group of other data.  For example, the total hours by pay period (charge_date) is equal to several different pay codes added together.  The first calculation is 'Worked Hours' and the calculation is:

                 If(Trim pay code="CB";charge_hours;If(Trim pay code="reg";charge_hours;If(Trim pay code="inserv";charge_hours;If(Trim pay code="OT";charge_hours;If(Trim pay code="holpy";charge_hours;If(Trim pay code="OTO";charge_hours;If(Trim pay code="ETO";charge_hours;If(Trim pay code="LTB";charge_hours; If(Trim pay code="BRVMT";charge_hours;If(Trim pay code="MTECH";charge_hours;If(Trim pay code="orien";charge_hours;If(Trim pay code="otex";Round(charge_hours;1);0)))))))))))).

                 The hours displayed by pay period is a summary field that is the 'Total of Worked Hours' called  'Total Worked Hours'.   Then we have a field named WH by Charge Date which is a calculation: GetSummary ( Total Worked Hours ;charge_date).  From there we have the average summary field that is 'Average of WH by Charge Date.  

                 How can we change it so that it will average correctly?  Thanks for your time and I hope this makes sense.

                  

                 Kendall

                  

            • 3. Re: Average Summary Field Isn't Working as Planned
              philmodjunk

                   It actually is computing the average correctly. It's just not the average that you want.

                   You'll need to set up your own average calculation for this. Do you have a way to count the number of pay periods here? There are possible several methods for doing that, depending on the version of FileMaker and the design of your database.

                   If you can access such a count, you can set up a calculation that divides the total dollars by the number of pay periods in your report.

              • 4. Re: Average Summary Field Isn't Working as Planned
                klbloomfield

                     Phil,

                     We currently have FM Pro 11.  Any suggestions on how to set up this counter field?  I tried a simple summary field to count it but it the number is too high.  I guess I need to count the unique charge_dates since this is my SQL field.  Thanks.

                • 5. Re: Average Summary Field Isn't Working as Planned
                  philmodjunk

                       Here's one method that does not require ExecuteSQL and thus can be used with Older versions: How to count the number of unique occurences in field.

                  • 6. Re: Average Summary Field Isn't Working as Planned
                    klbloomfield

                         That worked.  Thank you.