I can't figure out how to do this and can't find any advice searching the forums. Can anyone help?
I have a table that contains student enrollment data for all of our 32 schools in our school district. The table has one record for every instance of a student enrolling in a school. Each record contains the following fields
Student ID Number
Enrollment Start Date
Enrollment Exit Date
So if the student enrolls at one school on Sep 1st, then his family moves across town and he disenrolls and then re-enrolls at the new school on Oct 1st, he will have two records in the table. The first record will have an enrollment date of Sep 1st and an Exit Date of Sep 30th for school A. The second record will have an enrollment date of Oct 1st for school B and a null for Exit Date.
We're trying to depict mobility rates within our school district, school to school.
I want to display a 32x32 matrix with each school on a row, and each school also in a column. The row would indicate the number of students enrolled in that school on a date selected by the user, call it the origination date. The column would indicate the students enrolled in that school on a different date also selected by the user, call it the finish date. So the user would select an origination date of Sep 15th, and a finish date of Nov 15th, and the viz would display how many students were enrolled at each school on Sep 15th and also where they ended up being enrolled by Nov 15th. Since 80%+ students don't move the diagonal of the viz would have the bulk of the count.
Anyway, the only way I can think to do this is to create four fields, two for user input
and two calculated fields
To get the origination school and finish school for each record I would compare the Enrollment Start Date and the Enrollment Exit Date to the origination date and the finish date and then fill the origination and finish school fields with a lookup of the School Name in that record, or would be null if the origination and/or finish dates are outside the enrollment period in that school.
Finally, I can't figure out how to create the user input fields for origination date and finish date. Is there an easy way, or an entirely alternate way to do this?