Jump to content

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


This topic is 6049 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Modified a few sentences (blush)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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().

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 1 month later...

This topic is 6049 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.