3 Replies Latest reply on Aug 11, 2014 9:05 AM by philmodjunk

    Average of a field value in specific records



      Average of a field value in specific records


           Hi all,

           I know this topic is being discussed again and again but I could not find any post on this forum helping me out. Here's my issue:

      •           I have created a first table which includes two number fields (F1 and F2).
      •           Both fields can contain any figure.
      •           In Browse mode, I have created 4 records.           
        •                     record 1: F1=2 and F2=100
        •                     record 2: F1=2 and F2=200
        •                     record 3: F1=1 and F2=80
        •                     record 4: F1=1 and F2=120
      •           I would like to use the first field (F1) to calculate separately the average of F2 (150 for record 1 and 2, 100 for record 3 and 4).
      •           This calculation should display in a second table in a new field (F3).

           I have tried the calculation and summary option but failed miserably so far. With the calculation option, I am struggling with using/combining pre-defined formulas to build something similar to a "vlookup" or sumif or averageif formula. With the summary option, the problem seems to be that calculations are possible for fields available in the same table where I have created my summary field (2nd table) - hence cannot run calculations using fields in my first table.

           Can anyone help?

           Thanks in advance for your feedback.

        • 1. Re: Average of a field value in specific records

                    This calculation should display in a second table in a new field (F3).

               Is the critical detail here and I need more information about a) why you need this value in a different table (because if we can find a way to get the results you want without using a separate table, this is simpler to set up.) and b) how that separate table is linked via relationship(s) to the table you describe here. That relationship might be a key part of getting what you want here.

               Also, what version of FileMaker are you using? If you are using FileMaker 12 or newer, we have an option, ExecuteSQL not available in older versions that can be used here.

               And what will you do with these sub totals? Some methods for producing the totals that you want here aren't the best option if you then need to use these values in further calculations in fields or script step so knowing what you will do with the values can help avoid suggesting something that won't fully meet your needs.

               Possible options:

               a) don't use a separate table, use a summary report with summary fields

               b) Use a separate table with filtered portals and summary fields to display the needed sub totals

               c) Use a relationships that match to different table occurrences of your data table where each relationship matches to a desired group of records in order to compute these values

               d) Use a calculation field or fields that use ExecuteSQL to compute the needed sub totals.

               I can focus on one of the above methods and describe it in detail to produce what you need once I understand more about your solution and what you need to accomplish here.

          • 2. Re: Average of a field value in specific records

                 Hi and thanks for your answer. Unless there is a simpler way, I need the calculation to be reported in another table because I want to consolidate the data and use it for further calculations which need to display visually in a new layout. F1 relates to SKUs, F2 to quantity and F3 should sum quantities by SKU. In the new layout, I would like to display each SKU, related total quantity and further calculate corresponding costs.

                 I don't know if this helps.

                 Thanks again for your help

            • 3. Re: Average of a field value in specific records

                        I want to consolidate the data and use it for further calculations which need to display visually in a new layout.

                   This does not require physically consolidating groups of records into a single record in another table. You can produce the same end result with a summary report and you can still use the results in further calculations.

                   Set up a new layout based on your current table. Remove the Body layout part and replace it with a subsummary layout part "when sorted by" your SKU field. Put the SKU, any name or description fields that you want, and a summary field that computes the total quantity of your quantity field in this sub summary layout part.

                   Peform a find for the records you want and then sort them by SKU using Sort Records.

                   You'll now have one row of data for each SKU with a total quantity for each SKU.

                   Additional summary fields can be added to compute additional aggregate values based on each group of records with the same SKU.

                   Calculation fields can be defined that use the GetSummary function in order to use these subtotals from summary fields in additional calulations.

                   And here's an alternative approach:

                   It seems very possible that you already have another table where you have one record for every possible SKU. If not, it's pretty easy to use Import Records to create such a table. If you link this table of products to the original table by SKU, aggregate functions can be used in calculation fields to compute totals, averages, etc from each group of records with a matching value in the SKU field.

                   There are two key differences with this approach.

                   a) You can include an item in your list of subtotals that shows a quantity of zero where this item would be totally absent from a summary report.

                   b) The relationship links to all records in your original table that have a matching SKU. To match to just selected records--such as all items sold in a given date range, requires additional design work such that the relationship matches only to records that meet this additional criteria where in the summary report, you can easily report on many different groups of records simply by performing finds with different search criteria.

                   Here's a tutorial on summary reports if you are interested: Creating Filemaker Pro summary reports--Tutorial