topic Re: Serious set analysis using date fields....I think. in QlikView

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

Wed, 16 Nov 2011 17:03:26 GMT

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"

CenterName	Enrollments
CenterName1	53
CenterName2	134
CenterName3	12
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

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

johnw — Wed, 16 Nov 2011 19:40:12 GMT

Attached is an example of counting employees by year given their hire and fire dates. That seems like the same thing as counting students by month given their start and end dates. There are several variations on the theme included. I realize your situation is a little more complicated than this. If you can't figure out how to adapt it, let me know, and I'll look more closely at your specific problem.

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

Wed, 16 Nov 2011 21:33:24 GMT

This is very helpful! Thanks!

As you can imagine, it will take a little time to try and implement this. My only concern is that I have something on the order of 80K enrollment records. I think the avg number of days a student is enrolled is close to 200 days. That's like 16million rows if I did a day by day approach like you have done. this is considerably more processesing than the combination of all my other tables.

Maybe I can try to modify it to work on a month by month basis and not day by day.

Either way, thanks for the idea! And some set analysis that works and is related to my topic!

-Wes

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

johnw — Wed, 16 Nov 2011 22:43:43 GMT

Month by month should do the trick. Our biggest application has a table kind of like this with 65 million rows on it, but it's best to avoid that sort of thing if you can.