7 Replies Latest reply on Jun 26, 2012 7:58 AM by MorkAfur

    How to do a simple average?

    MorkAfur

      Title

      How to do a simple average?

      Post

      I have a table called ReportedErrors.

      In that table I have a count by date of the number of errors reported.

      What I'd like to be able to do is summarize - average by month and year.

      What's happening is that after I created a MONTH and DAY calculation, I can get indivdual ReportedErrors to appear broken out (in a report layout) by month and year (format 201206).

      But, no matter what I do (adding an additional sub-summary part, etc.), I can't seem to get an average of the values by month and year.

      What I'd like to do is this..

      Date          ReportedErrors

      6-1-12         100

      6-10-12       200

      6-15-12       300

      6-20-12       400

      6-30-12       500

      =======

      Average = 300

       

      --------------------------

      I've tried creating average calcuations for the ReportedErrors field, but nothing so far has worked. What prints out for the "average" is just the last value in the series for that month. So, using the example above, I'd see "Average = 500".

      Sure I'm missing something simple here...

      Thanks in advance.

      -m

        • 1. Re: How to do a simple average?
          GuyStevens

          Have you tried adding a summary field that summarizes the average?

          Like so:

          http://dl.dropbox.com/u/18099008/Demo_Files/Average.fp7

          • 2. Re: How to do a simple average?
            MorkAfur

            I wasn't able to download your file since my company's policy prohibits downloading (potentially) executable files.
            ------------------

            I have an Average Errors calculation field which is just "Average(Errors)".

            I have a Year and Month calculation field with is simply Year(Date) & Right ("0" & Month(date); 2)

            In my report, I have a sub-summary part that I group the report by Year and Month.

            The report correctly shows the detail records, grouped by year and month.

            Where/how would I add the Summary field you were suggesting?

            If I add a Summary (average (errors) field) to the database, for example, FMP 12 then tells me that I am not allowed to use a Summary when I select it via the "Part Setup" screen.

            Therefore, I'm still not sure how to summarize each YEAR+MONTH's data to just get an average of the detail's "Errors".

            Thanks in advance for any update.

            - m

            • 3. Re: How to do a simple average?
              GuyStevens

              Go to File - Manage - Database, Go to the second tab "Fields" and create a new field called s_Average (so you know it's a summary field) and set the type to "Summary"

              Then choose "Average of" Reported errors. Like so:

              Average

               

              Then go to your layout and put this average value in your sub summary part. Sort according to the correct field and this should work.

              You could also make a summary field that summarizes the "Total of" reported errors. And add that field to the sub summary part.

              • 4. Re: How to do a simple average?
                Sorbsbuster

                "I have an Average Errors calculation field which is just "Average(Errors)"."

                This will indeed give you the value for the last record (in a trailing sub-summary part), but what value it gives depends on what the field 'Errors' is defined as.

                • 5. Re: How to do a simple average?
                  MorkAfur

                  Got it working, thanks! (forgot to make the Errors field numeric.)

                  -----------

                  One final question -- I'm having difficulty charting the averages on a graph.

                  If I select YEAR/MONTH for the x-axis and s_summary for the y-axis, the graph doesn't show the summarized data.

                  Is there an additional trick needed here?

                  Thanks again.

                  -m

                  • 6. Re: How to do a simple average?
                    philmodjunk

                    The chart setup looks different depending on whether you use FileMaker 12 or an earlier version, but both have an option to chart "groups of data when sorted". You need to specify that option and then make sure your records are correctly sorted--it's a bit like getting sub summary parts to show data in a summary report.

                    • 7. Re: How to do a simple average?
                      MorkAfur

                      Turned out to be a sorting issue. I corrected the sort and assigned a script trigger to the chart layout and it looks great now.




                      Thanks very much.

                      -m