4 Replies Latest reply on Nov 16, 2015 7:19 AM by skywillmott

    calculating average for this month


      I think there is a complex formula that might be needed for this but...  i am trying calculate an average number for a single field based on three time frames.  This week (weeks tarting saturday), this month (regardless of what day it is in the month), and this year (calendar year regardless of where we are in the year).  I am sure current date and get will figure into this somehow but if you have a formula that will do please let me know.  For my db the field name is "Reading" and it is a numeric value only.



        • 1. Re: calculating average for this month

          (I deleted my original reply as I made a mistake☺ )


          Here's how I'd do this. If i need a report that shows the average (or min, max, total, whatever) I'd create a summary field. In this case, call it "Average" and set it as a summary type, which finds the average of the "reading" field.


          In the sample I have here, I created three other fields that capture the reading date's week in one field, month, in another and year in a third.


          I then created a report layout with sub summary parts. In these parts, you choose a field that the records will be sorted by. This is called a break field. Inside this part is the "Average" field. I put the same average field in each of the three sub-summary parts (week, month, year) that I created.


          my script then searches the fields for the Week we are in, month we are in, or year we are in. (Note, FM natively thinks Sunday is the first day of the week).


          Test this out. There are two versions of the report. One called "Report" that shows the readings for each day. And another called "Just Averages" that shows just the sub summary area parts.



          If you need a different UI of the average of what you're looking for, we can look at other options. This is a good way to get you started.

          • 2. Re: calculating average for this month

            I am programming fore a iPhone interface using fmgo.  I need three fields not a report.  One average for my readings for this week, this month, and this year.  A report is great on the desktop but my interface is being limited (my design) to a single form page which is where i enter my readings and then i want to see the averages for those time periods.  thanks for the suggestion though.

            • 3. Re: calculating average for this month

              Have you used ExecuteSQL()? You *can* set a field to find the average of your readings by using the function. You can either make a calculated field or a static field that is updated by a script. I prefer the latter.


              Something like:

              Week's average:  ExecuteSQL("SELECT AVG(Reading) FROM YourTable WHERE WeekNumber = ?" ; ""; "" ; WeekNumber (get(CurrentDate)) )


              Month's Average: ExecuteSQL("SELECT AVG(Reading) FROM YourTable WHERE ReadingMonth = ?" ; ""; "" ; Month (get(CurrentDate)) )


              Year's average : ExecuteSQL("SELECT AVG(Reading) FROM YourTable WHERE ReadingYear = ?" ; ""; "" ; Year (get(CurrentDate)) )


              These could be used to set the three fields you have: AvgWeek, AvgMonth, and AvgYear each time you enter a reading.

              • 4. Re: calculating average for this month

                You could use some ExecuteSQL functions to get the averages based on the reporting date and calculations for the week, month and year start dates.


                See attached example... You will see a table called 'Readings' which simply stores records with a date and 'readings_value' number value.... Then there is a 'System' table which has the calculations....


                Hope this helps :-)