I have a school which takes attendance. I created a calculated field which sets itself to "1" if it's an evening class and if it's Monday. Another for Tuesday. And so on.
Then I made a report which looks at attendance and sorts by week and then uses a summary field to total the number of students who attend per week.
Work great. Numbers add up fine.
The client asked for an average of those. I tried making a summery of the summary fields to get an average and it didn't seem to work, so instead I created a calculated field which which matches the summary field using this code:
GetSummary ( Monday_AnyEvening_Total_s ; StartOfWeek_c )
These numbers work fine and match the numbers in the summary field.
Then I created a new summary field which is an average of my new calculated field... and the math becomes super wonky (see screenshot below). I'm not even sure where to start to track this down. There shouldn't be any floats anywhere (it's just totaling how many number 1's there are and then averaging it).
Are there known issues around this? Is there possibly something I'm doing that triggers this?
Hi @JasonMark
I'll start with the disclaimer that I am not convinced that I understand the setup correctly.
But, if I do, my thought is that I would not try to solve this with an implementation that requires a Summary of a Summary. I understand that it is an interesting question as to whether it should or should not work (and why?), but dodging that for a moment, I think I would be more inclined to do some basic arithmetic on two summary fields, rather than trying to nest the aggregation feature.
I'll attach an illustration of one such approach. Certainly it is not the only such approach.
Also, the other disclaimer is that, because this is based on performing on-the-fly aggregations of a found set, the performance is not going to scale well -- as the found set increases in size, the time to wait for the calculations to complete will become less and less usable. You probably are already aware of that constraint, but I feel compelled to mention it anyhow, so that anyone reading this has fair warning that the idea presented here is more academic than it is practical.
All that said, I hope it helps. The key to the file that I am attaching, is to create a field that determines the number of distinct weeks that have been summarized.
If I understand the post correctly, and if I have not made any arithmetic blunder, than I believe that that "week count" value, along with a total attendance value, should get you the desired "average attendance per week".
Attendance.fmp12.zip (70.2 KB)
Thank you this is helpful and I really appreciate all the time you've put into it.
In terms of scalable loading time, that's not a big issue since they look at it by school year, and it takes 10 seconds now, so it will never take over 15 seconds for a report they run maybe 20 times a year.
That being said, I'm definitely not tied to this way of solving the problem. My situation is a little more complex in that the attendance has all days of the week, and we need totals by day.
I've attached an updated version of your file which shows the bug, and how far I was able to get with using your solution. The place I'm stumped on with your model is if there's a holiday or a snow day that shouldn't count towards the averages, so I can't use one "week count" I need a separate week count based on day of week.
Please let me know if you have any other suggestions, either as to what causes the math error, or another way to get the totals.
Attendance.02.fmp12 (248 KB)
Hi @JasonMark
I was concerned that there might be some days/weeks that should not be tallied in the count because they are holidays (or something similar). It sounds as though this is the case.
If the above is the current crux of the matter, then the next thing that I would be looking at is where, in the solution, is there a record of the holiday/snow days that should not count, and seeing if we can access that data to adjust the week count for each day of the week. Is there a table of holidays that can be accessed?
The idea is to adapt the final formula to:
Average Weekly Attendance = Total Attendance / Adjusted Week Count
Where: Adjusted Week Count = Week Count - Holiday Count
and of course, all of this needs to be broken out and replicated to be specific for each day of the week.
Again, this is somewhat academic, and part of me feels that it is not quite right to encourage you to continue down this path, as I feel that there probably are better ways to solve this, it's just that some of those better ways would probably require a larger re-thinking of how the attendance data is being stored.
Attendance_02b.fmp12.zip (72.0 KB)
EDIT: It just occurred to me that the example could use a reduced number of fields by using the same basic ideas to create an "IsCountableWeek" field. Again, you'd have to have seven of these; the concept is only illustrated for 1/7 in the example file. HTH.
Attendance_02d.fmp12.zip (71.3 KB)
I think I'm going to go with SQL for this.
Will be a lot easier at this point I think. I can come up with the week count using this"
ValueCount (
UniqueValues (
ExecuteSQL ( "SELECT (DateAttend) FROM Attendance
WHERE
DateAttend BETWEEN ? AND ?
AND DidAttend_Tuesday_c = 1
"
; "" ; "" ; $StartDate-1 ; $EndDate+1
)
)
I like where you're going with the "countable week" but it's not as straightforward as a countable "week" as each day may or may not be countable, so it needs to be daily, so it would have to be sorted as such. There is a holiday lookup table, but night classes are sometimes cancelled last minute and might have classes on days that "normal" school isn't in.
The team that runs it isn't the best at recording things so even if I made a second holiday list for night courses I'd need I'd need a routine to check for errors in that, so I might as well just use that.
1 Like
I very much appreciate how that approach is going to obviate the need to have numerous added fields to support counting x 7.
Thanks for staying that @steve_ssh, it was the prompt I needed to remember to go back and delete all the fields with errors in them... 
1 Like
@steve_ssh also, to your point, it's slower with the SQL, but it's an infrequent report and everyone is local so even if it takes 8 seconds for the screen to load it's not the end of the world.
1 Like
