Help! - script or calculation for a running average

[edddiec]

I have a database for my students - this includes a number of assessments - say 10. How would I be able to get an average score for each student bearing in  mind that some students would have submitted all assignments while others would have submitted only some assignments. I want to be able to calculate the average based on the assignments they have submitted.

 

Any help gratefully received

 

Regards

Edddiett

 

[Ocean West]

Automatic message

This topic has been moved from "Articles, Tips, Techniques & Solutions" to "The Left Brain".

[doughemi]

The grades should be in a separate table, with a student ID field and a grade field.  Each record should contain one grade for one student (along with other data such as assignment ID, assigned date, completed date, etc, if desired.)

 

Relate the tables by Students::StudentID = Grades::StudentID.  In Students, create a calculation field = Average(Grades::Grade).  

[jbante]

If you find that performance becomes a problem with the calculation field in the Student table, consider making it a number field instead and setting it via script after you add each assignment grade. This way, the field wont have to re-calculate every time you load a Student record. (Since the Student::gradeMean calculation would reference data in a related table, it would necessarily be an unstored calculation and hence re-evaluate every time.)

 

I doubt you'll run into this with a student grading situation, but if even that takes too long to calculate, you can update the existing average with only the new grade using a calculation like this:

 

Set Field [student::gradeCount; Value:Student::gradeCount + 1]

Set Variable [$weight; Value:1 / Student::gradeCount]

Set Field [student::gradeMean; Value:Student::gradeMean * ( 1 - $weight ) + $newGrade * $weight]

 

This approach requires a new Student::gradeCount field to get the same result as Average ( Grade::grade ) would. I write out $weight as a separate declaration because I sometimes have applications that use a constant rather than the number of values, which creates an exponentially weighted moving average — older values have progressively less influence on the average than newer ones.

[LaRetta]

Hi Jeremy,

I understand and agree to writing data whenever possible ( over unstored ) but I worry about using static data when the result depends upon values in other records. Can you explain how you would protect from other-record value changes or record-deletions which might skew the result and how you keep the data in sync?

One can use trigger for grade value changes and deletion script but if the aggregate changes you must loop them all ( if I understand this requirement) and you risk 1) slowing system while you write and 2) possible locking to address. If you wait until night, the data is out of sync for rest of day.

I see systems quite often that are out of balance in their attempts. And yet twice in a single week, you and Wim have both suggested just that and I greatly respect you both so I want to understand the protections and methods to sync that you implement. Maybe Eddie's thread isn't the place for such a discussion but since it would effect him, it probably IS the right place.

[jbante]

As with so much in life, it's a matter of tradeoffs. If Eddie's data sets are small enough, he'll barely notice the difference and may as well use the simpler solution of the unstored calculation. When the operation becomes a performance bottleneck, it becomes worthwhile to deal with it using more elaborate methods, with the caveat that more moving parts means more opportunities for bugs. It's harder to make a quiche than scrambled eggs, and you have to break some eggs to make either.

 

"One can use trigger for grade value changes and deletion script but if the aggregate changes you must loop them all... ." I'm not sure what you mean by this. Do you mean that if the Student::gradeMean field is modified by accident that the Grade::grade fields must be re-averaged? Yes, but I would hope that making the Student::gradeMean field non-editable during data entry would be part of the setup — that could be an easy-to-overlook source of errors, but using Average ( Grade::grade ) when setting the field would rectify the situation each time an assignment grade is added or modified. Re-calculating each student's gradeMean at choice intervals, such as your nightly suggestion or before printing report cards, is another way to limit the impact of update bugs. In either case, I get to be in control of when to incur the performance cost rather than letting usage patterns and FileMaker decide for me.

 

There's a good chance Eddie will be fine with an unstored calculation. It's hard to get wrong. But I've worked on more systems where unstored calculations over related records are a liability than an asset (and I imagine Wim probably has, too). Bottom line, I think Eddie should try the simplest solution first, and be ready to try progressively less simple alternative approaches if that doesn't work.