11 Replies Latest reply on Apr 22, 2013 10:17 AM by philmodjunk

    How to calculate averages...



      How to calculate averages...


           Hello, I´d like some help on solving the following issue on FM Pro 12, I´ve seen many videos and visited this forum, but I can´t seem to solve it:

           I need a simple average for all the records in one field and I also need to chart it.

           I´ve already used the "Summary" - "Average of" procedure but it doesn´t work.


        • 1. Re: How to calculate averages...

               I would suggest creating a simple test database with your summary fields - something easy to evaluate at a glance.

          Home > Designing and creating databases > Creating a database > Defining database fields > Defining summary fields

          What does Weighted By (or Weighted Average) Mean?

          Average of Data in Separate Fields

          How to use Calculation and Summary fields | lynda.com tutorial

          Aggregates (Summary Fields) in Filtered Portals


          • 2. Re: How to calculate averages...

                 "it doesn't work" doesn't tell us what results you DID get and that can provide a key clue as to what needs to be done differently.

                 What options did you specify for your chart?

            • 3. Re: How to calculate averages...

                   Thanks David and Phil for your quick response, when I mean that it doesn´t work I mean that, as you can see on the picture (below the column and on the graph, it´s in Spanish sorry), I need an average of grades that goes from 1 to 10 and it does not give me the average of those grades. If you can help me first with those average fields that are located below the columns, I believe I can understand why my graph does not give me the results I wants.

                   I really appreciate your help, and thanks again.

                   Sergio Lizarazo

                   P.D. Don´t know if it´s too confusing but the picture shows how I tried to calculate the average of those grades...


              • 4. Re: How to calculate averages...

                     Your summary field is defined in a table named "Notas". Your layout is based on a table occurrence named "DetailsEstudiante".

                     What is the relationship between these?

                     A summary field returns a summary value such as your average that is controlled by the context in which you use it. If you go to a layout based on Notas, you'll get averages based on the current found set and possibly how you have sorted those records. If you refer to the summary field from a related table, the relationship will match to a specfic set of records and the summary value returned is calculated from that set of related records.

                • 5. Re: How to calculate averages...

                       Hi Phil, I just got the time to read what you said on the previous message and tried to create a portal so there wouldn´t be any issues. However, I got the same result, the same average. I´ll upload the database relationships so you can help me check what I did wrong.

                       And thanks again.


                  • 6. Re: How to calculate averages...

                         As you have discovered, adding a portal to your layout will not change the results produced from the summary field.

                         Right now a reference to a summary field defined in Notas from a layout based on Estudiante will return the average of all records in Notas that link to the current record in Estudiante by the EstudianteID fields. And records with missing values in the field being averaged will be excluded from the average calculation. (They won't be treated as zeroes.)

                         What results do you want to see for your chart? (I can't read your language.)


                    • 7. Re: How to calculate averages...

                           I´m sorry to bother you again, but Is it possible to get an average of each criteria ("Recuerda", "Explica", "Relaciona" and "Comunica") using a determined time period, and not of all grades found in "Notas"? If so, what steps do I need to follow?.

                           The graph also tries to show the same averages in the same determined period of time.


                           P.S. The language is Spanish.

                      • 8. Re: How to calculate averages...

                             I figured it was Spanish or Portugese, but since I can't read either, it's all "greek" to me. wink

                             Take a look at the the following thread. It describes selectively computing a total (Sum), but this is the same "aggregate value" issue that you get with an average. So the discussion that describes several different ways to selectively sum values also applies to selectively averaging values.

                        Sum_Calculation based on condition

                        • 9. Re: How to calculate averages...

                               Hi Phil, If I understood correctly (I hope so) there are are two ways to get an average based on a condition or criteria that you mention in that post: 1) by creating a calculation, and then creating a filter of the records, or 2) by using a summary field in one layout and then do a portal in a (more general and not directly related) second layout.  

                               If this is true, then I´ve already tried both ways with no satisfactory result. What I´ve tried now was the following: on the "Notas" (Grade) layout I tried getting an average of all the grades (no filters used, just a simple average) by using option 2) using a summary field of the "Recuerda" (Remembers) criteria and it didn´t give me a convincing result. Is it possible that a blank record is affecting my calculations?

                               Here is the picture of the result: 



                          • 10. Re: How to calculate averages...

                                 Sorry for jumping in so late.angel

                                 On your first post...

                                 If  you calcualted the result, by hand, what is the expected averages?

                                 I think there is some confusion on what you expect to happen.  I do speak Spanish and I am not sure what you expect.

                                 Habilidad: talent, hability  or in school perhaps major course study?  


                            • 11. Re: How to calculate averages...

                                   There are actually 3 basic approaches if you are using FileMaker 12. FileMaker 12 provides ExecuteSQL() as a third alternative.

                                   All of these methods require matching values in such a way that you get just the group of records that provide the data you need to average.

                                   A portal filter expression can limit the records to just one such specified "category". And then a summary field defined in the portal's table will compute the correct average. But this method is limited to a "read only" result. You can't access the resulting value in a calculation in a field or a script step.

                                   If, on the other hand, you define the relationship so that matching records are limited to a single category, then you will get the correct average and this average can be accessed for subsequent calculations or script steps. There are two ways you can do that, but both limit the links to records for a given student for a given category to provide data for the selective result.

                                   And ExecuteSQL can produce much the same results as this second option but without the need to make changes to your relationship graph in order to get these results.