Summing only some values from portal

[hughbacca]

I've asked a few questions on here recently. Thanks for all the help. Hopefully this will be my last...

 

I've got a portal on a page. It has two columns of numbers. The first number is student_attendance and the second is classes_that_week.

 

What I want to do is figure out a student's attendance as a percentage of how many classes they should've had so far.

 

My calculation would look like this:

 

attendance percentage

= (sum(student_attendance)/sum(x))*100

 

where x is classes_that_week that have corresponding student_attendance values. That's the bit I'm stuck on.

 

I hope it makes sense.

[Ender]

I don't quite get what "a percentage of how many classes they should've had so far" means, but if you want to aggregate a subset of related records, the best way to go is to use a separate relationship (a separate table occurrence) that's filtered by additional criteria that limits the related set by whatever conditions you want.

 

For example, if you want to total only records for the last week, you could use a range-to-date relationship to show only the records for the last week, and use a sum() on that. The relationship might look like:

 

InterfaceData =

Interface::PrimaryKey = Data::ForeignKey

AND Interface::cTodayDate ≥ Data::Date

AND Interface::cAWeekAgoDate ≤ Data::Date

[hughbacca]

Thanks for thee prompt reply.

 

I'll try to explain myself more clearly. I have two fields in my portal. It might look like this (except it'll be vertical):

 

classes_that_week 5 5 5 4 5 4 4 4

student_attendance 5 5 4 4 4

 

 

classes_that_week is how many classes were held in each week. student_attendance is how many of those classes the student attended.

 

What I want to calculate is what percentage of classes the student has attended so far. In the example above, the student has had only five weeks of classes and attended 22 classes out of a possible 24.

 

How can I get filemaker to only sum the classes_that_week values that have associated student_attendance values?

[Ender]

I guess that would just be:

 

sum(table::student_attendance) / sum(table::classes_that_week)

 

Format the result as a percent.

[hughbacca]

The problem with that is that I don't want to include classes_that_week values that don't have a student_attendance value.

 

For example, in my previous post, the last three 4s in the classes_that_week field need to be ignored for the purposes of summing because no data has yet been entered in the corresponding student_attendance fields.

[Ender]

Gotcha. So you will need to filter the relationship, so that only those with attendance records are shown. When the relationship is filtered, then you can use the sum()/sum() calc.

 

For filtering, you'll need an indexed match field on the child side. If the student_attendance field is indexed, you can use that directly in a range relationship (interface::gZero

 

If student_attendance is not indexed, like if it's a calculation based on related values, then you might be able to relate directly to the source table instead (if the matching criteria is present there) and use the same kind of aggregate calcs on that. Or use a scripted process to update a flag field and relate to it as given above.

[hughbacca]

It works! Thanks for that smiley_cool