Average a field's values for each and every month since database began

[Dr. Zathras]

We have a database going back 15 months, with fresh data being added almost daily. I need to get the average value of a number field for each and every month from the date the database began up to the curreht month, then feed this into 24U SimpleChart as a series of x & y values = month/year & average score for that month.

I'm still trying to figure out where to start. Does anybody have any experience with anything similar?

Thanks.

Colin Hunter

[gdurniak]

create a new table, with 15 month records, and relate it back by year-month

these 15 records can then show the aggregate average by month

greg

[David Jondreau]

Create a calculated month and year field with a numeric result (200708; 200709, 200710, etc is probably a good format). Create a Summary field that is the average of the score. Create a layout with a subsummary part when sorted by the YearMonth field. Add your Summary field to this part. Find all your records and sort by the YearMonth.

[Dr. Zathras]

Thank you very much for the suggestions. I can see how these solutions would work for the existing data, but the database is still active. I therefore need the monthly averages to automatically add a new value for each new month going forward.

[Vaughan]

"I therefore need the monthly averages to automatically add a new value for each new month going forward.

Make a new record for the new month.

[comment]

If you follow David's advice, you won't need a table of months. Any month values - new or old - will be automatically calculated from the date. This is assuming your basic data unit (the one you're averaging) has a date associated with it. Otherwise all of this makes no sense.

To gather the summary data into a single field you will need either a script or a custom function. If you provide a more precise description of the required format, rather than just "a series of x & y values = month/year & average score for that month", we might be able to help more.

[LaRetta]

Create a calculated month and year field with a numeric result (200708; 200709, 200710

I can't help but speak up here. It is true that this will provide a cool way to properly sort and summarize your dates. However, if you instead create a DATE calculation of:

dateField - Day ( dateField ) + 1

... then you have a true date where ALL records are normalized (ie, brought to the wire simultaneously as the first day of its month). What is the advantage here? 1) FM always sorts the date in proper sequence and 2) for reports, that calculation (because it is a date) can be formatted at layout level as custom September, 2007 or 2007-09 or anything required (which can be changed based upon each specific report or layout need instead of hard-coded within a calculation)

It provides the best of ALL worlds because, in addition to these benefits, it provides a wonderful relational filter based upon :great: or :less: and many other uses unspecified at the moment. You will be surprised how many times you will re-use this same calculation for many things, including value lists for searching, repetition use (for columnar reporting) etc ...

nb: method learned from Michael (Comment)

LaRetta

Edited October 9, 2007 by Guest
Modified a few sentences (blush)

[Dr. Zathras]

Here’s a more detailed description of what I have and what I’m trying to do. We have a very simple database running on FMP 7 Server Advanced which allows people to answer three survey questions over the web. The answers to each question are 1, 2, 3 or 4, and when the respondent submits their answers a new record is created and date stamped. All data input is over the web, and all browsing is via FMP 8 or 9 client.

I’ve already put together the calculations for 24U SimpleChart to display a pie chart for the answers to each of the three questions (% of total who answered “1”, % of total who answered “2”, % of total who answered “3”, % of total who answered “4”). This displays the results graphically for the records being browsed. I now want to add a vertical bar chart where the x-axis = months (e.g. 7/2006, 8/2006. 9/2006, ….) and the y-axis = average score for that month.

Using David’s and Laretta’s help I’ve put together a layout with sub-summary breaks for each month showing the average score for that month, but how do I extract and gather that data to two fields, “months” and “sub-summary for the month”?

I hope this better describes what I’m trying to do. I really appreciate the help you’re offering.

[comment]

You still haven't said much about the format that the plugin requires (except that now it seems it to be two fields). I suggest you post an EXACT example of how the result should look for the plugin to accept it.

[David Jondreau]

Look up the GetSummary() function in the Help file.

[Dr. Zathras]

SimpleChart can take its data input in a number of different ways, but for my purposes I'm using one field for x values and another field for the y values. The fields contain each data point separated by a ";"

As an example, for the period July 2006 - October 2006, the x-axis data looks like:

7/06 ; 8/06 ; 9/06 ; 10/06

The y-axis data look like:

3.2 ; 3.8 ; 2.5 ; 3.1

Each value in the y-axis field separated by ";" is the average of the scores submitted for that month. In the example above, the average for all scores for July 06 was 3.2, the average for August was 3.8, etc.

I'm going to take David's advice now and read up on GetSummary().

[Ender]

Here's a general description and algorithm for assembling summary record data into chart strings:

http://fmforums.com/forum/showtopic.php?tid/190507/

For FM7 compatibility, use global fields instead of $$variables.

Once you figure out your getsummary() average, you can pop that in there for the X data (how you're getting your average is not clear enough to me).

Edited October 9, 2007 by Guest

[comment]

Well, GetSummary() will provide one part of the solution. The other part is getting only one value for each group of records - and that is the more difficult part. As I said before, you can use a script for this, or a custom function. I'd suggest you use a script, since it's a bit easier.

Instead of me writing it out, let me point you to a technique known as 'Fast Summaries' by Mikhail Edoshin. It does almost exactly what you need, and once you understand it, you should be able to easily tweak it to your exact requirements. If not, post again here.

Fast Summaries tutorial:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

A newer demo file by Kevin Frank:

http://www.kevinfrank.com/demo-files-78.html

[comment]

Ender posted ahead of me, but he describes the same technique.

[Ender]

Hey, how about that. I didn't realize that was the famous "fast summaries" technique. Man, I gotta read more of those off-forum articles.

[Dr. Zathras]

Thank you to everybody for all your help with this. Your referenced example at

http://filemakertoday.com/com/showpost.php?p=42530&postcount=6

was so close to my needs that I was able to use it with very few changes, other than converting the variables to global fields to keep our FMP 7 Server happy.

I can already see other uses for the "famous fast sorting technique" to put together reports for our institution. Great, a new technique to add to my limited skillset.

Thanks, again.

Colin

[Ender]

Glad you got it working.

[ssaucedo]

This is exactly what I have been looking for...would you possibly be willing to fill in some details as to how I can actually then create a layout that will display the data utilizing this calculation?