3 Replies Latest reply on Nov 22, 2011 10:01 AM by philmodjunk

    average for last 7 Days



      average for last 7 Days


      I am very new to Filemaker and need to create on formula.

      I have one table with 12 fields.

      One is Date and reset are radio buttoms with the numbers 0 to 9.

      Everyday the you fill out all the radio buttons.

      I need to create a bunch of averages like the past 7 days.  The field will sit to the right of the radio boxes and get updated as the radio buttons are entered.

      Please try and keep it simple as I am still learning Filemaker.


        • 1. Re: average for last 7 Days

          What's the purpose of the 10 radio button fields with a single value for each field? Does the user select one radio button in each record or multiple radio buttons?

          Is the average that you want the average value (0 - 9) selected on each record?

          When you fill out this data, is it one record for every day? Are any days skipped (no data recorded on holidays or weekends for example?) If skipped days are possible, do they figure as a 0 in computing the average or should the average omit that record from the calculation.

          It's possible to set up a relationship that matches the current record to all records from that record's date to up to 7 days in the past. Then the Average () function can compute the average--omitting any records that are missing, but I need to know a few more details before I spell out how to set that up.

          • 2. Re: average for last 7 Days

            Each of the 10 fields are questions.  Each question has its own radio button and it's own average.  The average is the 0 to 9 per field.  The data should be filled out everyday but a day may get missed.  In that case it would be better to ignore that record.  


            I hope that helps.


            • 3. Re: average for last 7 Days

              Add a calculation field, c7DaysAgo with date specified as the return type:

              Date - 6

              Define this relationship:

              YourTable::Date < YourTable7days::Date AND
              YourTable::c7DaysAgo > YourTable7days::Date

              To create YourTable7days, open Manage | Database | relationships, select your table and click the duplicate button (two green plus signs). You can double click this new table occurrence to open a dialog where you can change the name.

              Drag  date field to date field to create a relationship, then double lick this relationship line so that you can change the = to < and to add the second pair of fields.

              Now a calculation field, cAverageQ1 can be defined as Average ( yourTable7days::radiobuttonField1 ). You can also define a summary field that computes the average of a radio button field (make sure radio button field is of type number in either case). You could then just add the summary field from Yourtable7days to your layout to get the same average.

              Note: It would be more flexible to put each question in a record of a related table. This is a more advanced approach, but makes it possible to reduce the number of calculation or summary fields and changing the questions, adding more, removing some all become simple data entry operations.