This is National Student Clearinghouse data.
This is almost a real student record with ID and institutions changed (with student ID=1, column name=Requester Return Field). The student went to 5 different universities, circling around to us once. My university was the 2nd and 4th that the student attended. The last column shows college sequence.
I would like to find, for each student, how many universities they attended AFTER attending my university. That means I have to get the calculation to skip to the last time the student enrolled at my university before beginning to count (skip 2 and go straight to 4 in the sequence column). For this student, the answer would be "2."
I would also like to know whether the student graduated from one of those universities after mine. In the case of this student, the answer is in column Graduated?=N.
There are two date columns that might help formatted as YYYYMMDD.
Thank you if you can make sense of this.
To find the last row for "UNIVERSITY OF HOUSTON-VICTORIA" do:
FIXED [Student ID] : MAX( if [College Name] = "UNIVERSITY OF HOUSTON-VICTORIA" then [Sequence Number] END) }
(Or do it for enrollment begin, or enrollment end, depending on whether you are looking for a date or a sequence number.)
With that you can do
{ FIXED [Student ID], [Sequence Number] : MIN( if [Sequence Number] > [LOD CALC FROM ABOVE] then 1 END) }
Now, for each row with a sequence number bigger then the last one for "UNIVERSITY OF HOUSTON-VICTORIA", you'll have a 1 in that calc, and you can use that to identify which rows to count up, analyze, etc.
