5 Replies Latest reply on Sep 23, 2016 7:07 AM by David Moyer

    Summarizing statistics on a found set

    alfredsenn

      For a given found set of records, I would like to extract for some variables summary statistics such as, count, min, max, average and standard deviation. I would like to do this through a script without creating new variables.

      The statistics functions (count, min, max, ...) used in a script will return the value related to the entire table and not for the found set. By creating a self-joined relationship between the entire table and the recods that are in the found set, the statistics are evaluated correctly. Setting up manually a variable in my table equal to one for the records that are in the found set is time-consuming. Is there a GET function that would identify the records in the found set

        • 1. Re: Summarizing statistics on a found set
          Johan Hedman

          If you have Statistic FIELDS you will get Min/Max/Count ... for found count of records.

           

          Get(FoundCount) will get you current found records

          • 2. Re: Summarizing statistics on a found set
            alfredsenn

            Thank you Johann!

             

            I would like to avoid creating Statistic Fields, fearing that this might slow down considerably the solution. I would have to create 5-6 fields per variable, for about 50 variables, this would be about 250-300 statistic fields.

             

            By doing this via scripts, I would have 1 script / analysed variable, which would be calculated only on demand.

            • 3. Re: Summarizing statistics on a found set
              Johan Hedman

              Statistic fields only slow things down if they appear on the layout. If you dont show them but instead just get there data into $$ you can just show merge variables in your layout

              • 4. Re: Summarizing statistics on a found set
                dtcgnet

                Summary fields are empty, and uncalculated, until they are used. They won't impact your solution's performance just by existing. They are also far more flexible than the route you're thinking of going down. For instance, let's say you wanted Count, Min, and Max for each value of a given field (like salesman). If you use a subsummary report, you could place your Count summary field, Min summary field, and Max summary field in a subsummary part, and those values would be calculated for each salesperson AND you could get other totals and a grand summary if you needed.

                 

                If you have all of those summary fields in a table, and they are only used on summary layouts, they don't hurt performance at all, and ON those layouts, they are incredibly easy to use and always accurate.

                 

                Before you go down the route of adding a self-join you won't need and creating a big script...study up on subsummary reports. With one subsummary report, you could sort by salesperson and get all of the breakdowns by that, then sort by state and get all of the breakdowns by that, and then...whatever you want.

                 

                My two cents.

                1 of 1 people found this helpful
                • 5. Re: Summarizing statistics on a found set
                  David Moyer

                  Hi,

                  FM can perform SQL queries on its own tables.  You might consider using the aggregate functions available there, although it doesn't have standard deviation.

                  https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf