1 Reply Latest reply on Aug 15, 2012 10:10 PM by Jonathan Drummey

# Filtering based on aggregate record counts

I've attached a demo packaged workbook to play with this problem.

I and trying to figure out how many students are enrolled at different schools.  We have 7 schools including four "primary" schools which I've labeled A-D and three "secondary" schools which I've labeled X-Z.  We have 100 students, each of which is enrolled at exactly one of the four primary schools.  In addition to those enrollments, a subset of those 100 students has an additional enrollment at one of the secondary schools.  So the underlying data has 130 records in it, one for each enrollment instance, some of which are secondary enrollments at schools X, Y, or Z in addition to the student's primary enrollment.

I need to be able to depict the number of students enrolled at each school, BUT... if a student is double enrolled at both a primary and a secondary school, then I need to show that student at the SECONDARY school instead of the primary school.  Ie. the enrollment count for schools A-D need to depict ONLY the students that enrolled solely at that school and do not have a secondary enrollment.  Then the enrollments for the three secondary schools X-Y need to depict how many students have that school as a secondary enrollment, but also ideally would depict for each of those students which primary school the students are coming from.

For a viz I was thinking of a table with primary schools A-D in the first column down the left and then secondary schools X-Z in the first row across the top, accompanied by a NULL choice too.  The intersecting cells would contain the number of students enrolled in school A-D and where they have a secondary enrollment.  For most students they would be counted in the column with NULL as a header indicating that they have only a primary enrollment.

Does this make sense?

Thanks,

Danny

• ###### 1. Re: Filtering based on aggregate record counts

Hi,

Attached are two different options. Both use a calculated field to determine the school instead of directly using the School dimension on Rows. Note that one student (#67) is in two secondary schools, the way this is set up that student is in School Z.

The 2nd version uses a table calculation that is set to Compute Using Student Number, with the blue School (revised) pill having unchecked "Ignore in table calculations".

Cheers,

Jonathan