7 Replies Latest reply on Jun 4, 2013 7:19 AM by philmodjunk

    Summary of data

    FileMakerNovice

      Title

      Summary of data

      Post

           Hello,

           I have a database that is fairly complex.  Let me provide an outline and description and then I will explain my issue.  I am leaving several related tables out to simplify what I am trying to do.  These are unrelated to the issue at hand.

           Tables:
           EmployeeStats
           Calls
           EmployeeInfo

           Relationship:
           EmployeeStats--EmployeeInfo
                                |
                              Calls

           EmployeeStats is related to EmployeeInfo:
           employeeID to employeeID

           EmployeeStats is related to Calls:  
           employeeID to employeeID
           StartDate<=Date
           EndDate>=Date
           OmitDates not equal to Dates

           So... Employee stats pulls from several other tables to provide statistics for an advisor.  For calls, we calculate a producitivity stat.  This stat is calculated by taking several fields, including, TimeOnCall, TimeTakingNotes, TimeResearching.  We then devide these by the total number of calls to get a productivity number.  

           My trouble is that my current method only provides a calculation of all the calls, regardless of what queue the employee is in.  The employee could be on T1, T2, Emergency call, ect.  In the "Calls" table, there is a "role" field.  All this data is imported into the database.

           My goal is to get a subsummary or something to calculate producitvity for each role.

           Let's take Employee A.  He had 40 calls.  Day 1, he took 5 in the T1 queue.  Day two, he took 10 in the T2 queue, etc.  In the reporting, each of these days has metadata, Time on call, TimeTakingNotes, etc.

           I currently, would only get the total of the time and then devide it by the total calls.  This is called "Productivity" and is located in EmployeeStats.

           What I'm wanting to do is get this productivity number for each Role.  Is this possible in my current setup?  How can I do this?  I've thought about summary fields and sort a list but there is no option for the fomula in summaries.  

           Any help is much appreciated.  Thanks.

        • 1. Re: Summary of data
          philmodjunk

               It would appear that if you either used a find or possibly Go To Related Records, you could pull up a found set of Calls records that could then be sorted by

               Employee
               Role

               To group the records first by employee, then by role.

               Summary fields could then compute totals and averages for each such sub group if you set up this layout with a sub summary layout part "When sorted by Role" and put the Role and summary fields inside that sub summary part.

               If you need to use some of those sub totals in a calculation, the getSummary function may be used to get the same subtotal that the summary field would display in the sub summary part so that this value can then be used as a term in a calculation.

               For more on this type of summary report: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Summary of data
            FileMakerNovice

                 Thanks for the reply Phil.  You sir, are always the one that provides the best answers.

                 I apologize but I am always a bit perplexed by summaries and this would be the first time I used one in this way.

                 So... with that being said, I read your link and it details and explains summaries very nicely.  Unfortunately, this is the part that I already understand.

                 With my database, I was wondering if you could clarify a little of what you said.  First off, the layout that will be sorted:  Will this be a layout using the "Calls" table or "EmployeeStats" table?  The next question is: What table would the summary fields be located?

                 To add a bit of complexity, Calls is actually two related tables.  I truly apologize for not mentioning this before.  Calls is "Call Stats" and "Calls".  This is due to the reporting that I pull.  Call Stats consists of the time on call, time researching, time taking notes, etc.  Calls consists of calls per day.  These are related by employeeID and date.  So:

            EmployeeStats--EmployeeInfo
                                 |
                               Calls Stats--Calls

            • 3. Re: Summary of data
              philmodjunk
                   

                        Will this be a layout using the "Calls" table or "EmployeeStats" table?

                   Calls

                   

                        [in] What table would the summary fields be located?

                   Calls

                   

                        To add a bit of complexity,...

                   That does add complexity. A summary field must be defined in the same table as the field that it summarizes. Then your report layout needs to be based on an occurrence of that same table.

                    

              • 4. Re: Summary of data
                FileMakerNovice

                     Awesome.  Thanks again for the response.  I believe I am on the right track but my calculations don't seem to be adding up.  Let me explain what I did:

                     Created a layout for calls:  Has a subsummary part when sorted by role.
                     Created a field called producitivity in Call Stats.  It calculates OnCall+Researching+(etc)/Calls in a summary field that is weighted by calls.
                     (To get calls from the calls table, I created a calculation field in CallStats that is simply "=Calls::Calls")
                     My script calls the layout and does a search for the employee in question.
                     The find also sets the date field to STARTDATE...ENDDATE using a variable.

                     Two issues that I have now:
                     1.  If there is just one role, the resulting productivity is not equal to the producitivity that is calculated in EmployeeStats.  The field in EmployeeStats does a sum of OnCall + sum of Researching + sum of (etc) / Sum of calls.  I do not get why these two fields are not the same.  
                     2.  I would also need a "Group Average" in each role.  I do not see a way to do this in the current setup.

                • 5. Re: Summary of data
                  philmodjunk

                       Sorry, but there's not enough detail here to suggest why you are getting differing results nor how you might set up a "group average" and my memory of past posts isn't good enough to reconstruct what you have designed here.

                  • 6. Re: Summary of data
                    FileMakerNovice

                         Thanks again for all the help.  I ended up just using what I had.  My assumption with the two results not being equal is that one is a weighted average and the other is a a straight up averaging of all the data.  The value is typically off 3/10ths of a minute or so.  It could be possible that the trailing decimals being used affect the result.

                         At any rate, I have to put this feature on the back burner and will revisit it later.  Again, thanks for the help.

                    • 7. Re: Summary of data
                      philmodjunk

                           If any of your values used to compute the aggregate value are rounded, a small discrepancy can also be due to accumulated round off error.