3 Replies Latest reply: Nov 16, 2011 5:43 PM by John Witherspoon RSS

    Serious set analysis using date fields....I think.

      Hello Qlikview Community!


      I need some help with a specific dataset I'm trying to grab. I am in the education sector where my data belongs to Students that belong to Math Tutoring Centers. (I'm pulling data at the HQ level for all centers)


      I have an "Enrollments" table that has the following columns:

      Id, StudentId, StartDate, EndDate, EnrollmentType [plus a lot more data that I don't care about in this question.]


      I am trying to count the # of enrollments based on a "month year" from a Master Calendar that has been populated with all the dates I care about.


      So basically, I want to be able to click on a value in the field CalendarMonthAndYear (currently in a listbox) and then give me a snapshot of the # of enrollments for that month.You would define "an enrollment" for the month by: StartDate<=MonthEnd(CalendarMonthAndYear) AND EndDate>= MonthEnd(CalendarMonthAndYear).


      It is possible for the students to have multiple concurrent enrollments with different EnrollmentTypes. If this is the case, It would be optimal to only care about the student having at least 1 active/valid enrollment, not that they have two different enrollments for that month. However, it isn't vital that we count them once instead of twice.


      To make this a little harder, the StudentId on the Enrollments table has a relationship with a Students Table which has a relationship with a Center table. So I really want a snapshot aggregated by the center. My dataset would be something like this if you clicked "May 2005"





      (this would mean that CenterName1 had 53 students with an enrollment record with a start date <= 5/31/2005 and an end date >= 5/31/2005 while centerName2 has 134 students under the same criteria.)


      I've literally tried 10 different approaches to this problem. I've tried to think of a way in the load script to ease things, I've tried my limited knowledge of set analysis to no avail... If anyone could suggest a plan of action, it'd be much appreciated. I am familiar with the AGGR function and use it regularly.


      Thanks in advance!!!