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"

       

       

      CenterNameEnrollments
      CenterName153
      CenterName2134
      CenterName312
      etc...etc...

       

      (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!!!

      -Wes