(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.
AverageByAnything.fmp12.zip 73.1 K
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.
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.
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.
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 :-)
Averages.fmp12.zip 69.2 K