A count based on a selected field

[#1]

Hi,

I'm working on a school attendance report, and I sincerely hope you can help:

Days absent (A)	PersonID (B)	Attendance stat (C)	AbsenceType (D)	SchoolName (E)
1	1		Valid	Oxford
1	2		Not valid	Oxford
4	3		Valid	Cambridge
2	4		No reason given	Essex
-	5		Not valid	Cambridge
2	6		No reason given	Essex
4	7		Not valid	Cambridge

I need an expression for column (C): How do I divide the total number of days in column A (Days) on the number of students (count(distinct personID)) in the selected school(s) in column E (SchoolName)? It is important that the user can change the selection, for example by selecting just one AbscenceType code and still see the stats in column (C) based on the total count of students in the selected schools in column (E).

[swuehl]

You probably need to ignore your selections using set analysis, like

= sum([Days absent (A)])  / count({1} DISTINCT [PersonID (B)])

[vincent_ardiet · Accepted solution]

Hi,

You can use aggr to sum at school level and a set analysis to ignore the absence type, try something like this :

=aggr(nodistinct sum({<AbsenceType=>}Days),SchoolName)/count(personID)

Regards,

Vincent

[#4]

That did it, thanks!