topic A count based on a selected field in QlikView

A count based on a selected field

Mon, 02 Sep 2013 11:06:37 GMT

Hi,

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

Days absent (A)	PersonID (B)	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).

Re: A count based on a selected field

swuehl — Mon, 02 Sep 2013 12:18:43 GMT

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

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

Re: A count based on a selected field

vincent_ardiet — Mon, 02 Sep 2013 12:56:48 GMT

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

Re: A count based on a selected field

Wed, 04 Sep 2013 07:22:48 GMT

That did it, thanks!