4 Replies Latest reply on Dec 4, 2013 7:33 AM by neponset

    Calculating a geometric mean



      Calculating a geometric mean



           I have a table of water testing results for ten different dates (i.e. 10 records although the number of records involved is not always 10), and need to compute the geometric mean of the SamplingResult (i.e. a number field). To do this I need to multiply the 10 SamplingResult values and then take the 10th root of the product.

           PhilModJunk provides a nice solution to this problem when you are trying to take the geometric mean of a set of related records (see Calculating Stock Index Returns).

           Specifically, he proposes the following formula:

      Evaluate ( Substitute ( List ( ChildTable::Index ) ; ¶ ; " * " ) ) ^ ( 1/ Count ( ChildTable::Index ) )

      But because of the list function, it only seems to work with related records. I want to do essentially the same calculation, but directly on the current found set in my table. Am wondering if there is a better way to do this than creating a self-join and then writing a script that sets a key field to make the self-join work for the records of the current found set.

      Thanks for any suggestions anyone can offer! 

        • 1. Re: Calculating a geometric mean

               How do you produce the found set of records for this calculation in the first place? It may be possible to incorporate the same criteria in the self join relationship or, if using FileMaker 12, an ExecuteSQL() function call where you use ExecuteSQL in place of List in my earlier example.

          • 2. Re: Calculating a geometric mean

                 we would be getting to the found set just using a manual search request (as opposed to a script) though setting up a "generate report" button might be a nice way to do it. Will also look at the executeSQL function, have never played with that one. Thanks!

            • 3. Re: Calculating a geometric mean

                   My point was that if your user enters/selects criteria into a set of global fields and then a script uses that criteria to perform your find, you might possibly be able to use the data in the global fields in either a relationship or a SQL query to access the same set of records and now you can get your return separated list of values to use with Evaluate ( Substitute ( ...

                   Here's another approach:

                   Define a new layout with the same exact name selected in Layout Setup...|Show Records From. You might do that by using Duplicate Layout while in layout mode. Set up this new layout such that Index is the only field on this layout. When you perform a find on your original layout, this layout will have the same number.

                   Now a script can, after the find is performed (and this can be triggered by the user find), go to the other layout and use Copy All Records to copy the value of index for the entire found set and this will also produce the same return separated list of values that we need. Then it can paste this data into a global field which can then get the Evaluate ( Substitute (  treatment to produce this value. Get ( FoundCount ) can be used to count the number of records for the second part of the calculation.

                   Another method would be to use a looping script to loop through the records to compute the product so that you can then raise it to the 1/Get( FoundCount) power.

              • 4. Re: Calculating a geometric mean

                     More excellent suggestions Phil. Especially like the "copy all" approach which had never occurred to me. Seems like I'm stuck using a script either before or after the find (which is not such a tragedy in the end) but now I've got quite a few more options for how to do that, in addition to my original thought of using a looping script. Thanks!