amcrane
asked on
How to calculate averages based on other variables in FileMaker Pro 12
Filemaker Pro 12 question
I am a high school teacher and I’m looking into the possibility of a correlation existing between calendar age and academic perfomance at our semestered school.
I set up a FMP db with one table and all the fields in that one table.
I have four fields:
1) Month_of_Birth (radio buttons: 01 -12)
2) Student_Mark (#)
3) Level (radio buttons: D, P, IB, L)
4) Semester (radio buttons: 02, 06, 07)
I would like to calculate averages based on this data. I assume I need to use a calculation field, but I am at a loss as how to proceed.
For example, I'd like to calculate the average mark for those born from Jan to Jul (inclusive), who are Levels "D" and "IB", in semesters "02" and "06".
Obviously, I'd like also to calculate the average mark for those born from Aug to Dec (inclusive), who are a Level "P", in semester "02" as well as other combinations and permutations.
I figure I'd be able to modify the code if I could get something for the first one.
Any ideas on how to write a calculation for this? Or do I need something else?
Thank you for your time.
Alex Crane
I am a high school teacher and I’m looking into the possibility of a correlation existing between calendar age and academic perfomance at our semestered school.
I set up a FMP db with one table and all the fields in that one table.
I have four fields:
1) Month_of_Birth (radio buttons: 01 -12)
2) Student_Mark (#)
3) Level (radio buttons: D, P, IB, L)
4) Semester (radio buttons: 02, 06, 07)
I would like to calculate averages based on this data. I assume I need to use a calculation field, but I am at a loss as how to proceed.
For example, I'd like to calculate the average mark for those born from Jan to Jul (inclusive), who are Levels "D" and "IB", in semesters "02" and "06".
Obviously, I'd like also to calculate the average mark for those born from Aug to Dec (inclusive), who are a Level "P", in semester "02" as well as other combinations and permutations.
I figure I'd be able to modify the code if I could get something for the first one.
Any ideas on how to write a calculation for this? Or do I need something else?
Thank you for your time.
Alex Crane
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
The simplest way to go about this is to use FileMaker's sub-summary "Part" on a layout and the Summary field type. You can do it with relationships and Calculation fields but I'd start with sub-summaries. FileMaker has both Grand Summary and Sub-Summary "Parts" that can be added to a layout to show aggregate totals, averages, counts, etc. The Grand Summary Part - Leading or Trailing - as it's name suggests summarizes the entire Found Set of records. The Sub-Summary part summaries just portion of the found set, based on the Sort order.
So, for example, if you added a Sub-Summary Part (in Layout Mode use the Part tool next to the field tool at the top of the screen) that summarizes based on Level, then within that Sub-Summary Part, added a Summary Type field set to the Average of "Student_Mark", and then sorted the records by Level, the Sub-Summary part would appear each time there is a change in Level. The summary fields within that part for Average, Total, Count, etc., would then show values for those records just before or after it (depending on whether you specify before or after each group). You can also have multiple Sub-Summary Parts linked to other sort criteria. So, if you sort by Level and then Semester, you can have a sub-summary part for each. The first sort criteria will give you the values for just that group of records, e.g. "Level" and the second sort criteria will give values for all records in the larger group of "Semester", regardless of level.
The key to figuring out how to go about this is determining where your "break" points are, meaning which groups you want summarized. For example, when you say:
I'm not sure if that means you want separate averages for each level and each semester or you want an average for "D" and "IB" combined.
To start with, I would create a Summary Type field for Average of Student_Mark (and look at the other options) and then add a Sub-Summary part to a list view layout with that field in it. Sort the records by Level or Semester, which ever you specified for the Part, and look at the results.