topic Re: modelling data with several date dim in QlikView

modelling data with several date dim

Tue, 21 Jun 2016 23:53:31 GMT

I'm trying to figure out how to build this app

I've 2 sets of data

OnlineCourseEnrollment

studentID, EnrollDate

1,'2016-01-01'

2,'2016-01-01'

3,'2016-01-01'

2,'2016-02-01'

4,'2016-02-01'

5,'2016-02-01'

Certification

studentID, PassingDate

1,'2016-01-29'

3,'2016-02-25'

2,'2016-02-25'

Should I use two different date dim one for PassingDate and one for Enroll? What is the expression to use?

The questions are liked, given a date range, what is the % of student passing. For example.

How many percentage pass based on Jan, 2016 Enroll

(student 1,3)/(student 1, 2, 3)

=2/3 <- what would be the expression to use to determine this?

How many percentage pass for all time

(student 1, 2, 3)/(student 1, 2, 3, 4, 5)

=3/5 <- what would be the expression to use to determine this?

How many percentage pass based on Feb Enroll

(student 2)/(student 2, 4, 5)

=1/3 <- what would be the expression to use to determine this?

Should I combine them into one single table? liked

studentID, EnrollDate, PassingDate

1,'2016-01-01', '2016-01-29'

2,'2016-01-01',

3,'2016-01-01', '2016-02-25'

2,'2016-02-01', '2016-02-25'

4,'2016-02-01'

5,'2016-02-01'

Re: modelling data with several date dim

sunny_talwar — Wed, 22 Jun 2016 01:11:19 GMT

How is Jan 2016 2/3? 3 is graduating in Feb, no?

Re: modelling data with several date dim

Wed, 22 Jun 2016 01:35:36 GMT

I would say yes, join them into one table.

This will cover both the first and second question:

count({<PassingDate={">=[Starting Date]<=[Ending Date]"}>}distinct studentID)/count({<EnrollDate={">=[Starting Date]<=[Ending Date]"}>}distinct studentID)

Just replace the start date and end date with whatever figure you are after.

This will cover the all time question:

count({<PassingDate={"*"}>} distinct studentID)/count(distinct studentID)

With this one you can remove the 'distinct' if you want to included students that have enrolled twice (#2)

Hope this helps!

Re: modelling data with several date dim

Wed, 22 Jun 2016 03:01:11 GMT

Good point.

students enrolled in Jan are students 1, 2, 3.

OnlineCourseEnrollment

studentID, EnrollDate

1,'2016-01-01'

2,'2016-01-01'

3,'2016-01-01'

Certied ones

Certification

studentID, PassingDate

1,'2016-01-29'

3,'2016-02-25'

2,'2016-02-25'

so, Jan Enroll actually has 100% Cert. Of those, 2/3 did it in first try. 1/3 had to do it in second.

Back to the original question, how to model this in Qlik?

Re: modelling data with several date dim

Wed, 22 Jun 2016 05:06:15 GMT

Is this how it would be loaded?

studentID, EnrollDate, PassingDate

1,'2016-01-01', '2016-01-29'

2,'2016-01-01',

3,'2016-01-01', '2016-02-25'

2,'2016-02-01', '2016-02-25'

4,'2016-02-01'

5,'2016-02-01'

the expression =Count ({<PassingDate={"*"}>} DISTINCT StudentID)

is returning 5, while there are only 2 unique PassingDate and 3 people having PassingDate

Re: modelling data with several date dim

Wed, 22 Jun 2016 05:58:25 GMT

Please provide some more sample data.

or else merge both tables.

Re: modelling data with several date dim

Wed, 22 Jun 2016 06:50:02 GMT

try this it will give student id whose passing and certification month is same.

IF(MONTH(ENROL_DATE)=MONTH([CERTIFICATION DATE]),Count(Student_id))

Re: modelling data with several date dim

Anonymous — Wed, 22 Jun 2016 07:11:21 GMT

HI ben,

You have 2 options:

Use a canonical calendar.
Create only one fact table with one date only and one field to know if your record is Enroll or Cert

Regards!!

Re: modelling data with several date dim

Digvijay_Singh — Wed, 22 Jun 2016 12:51:24 GMT

I think your data is already associated using studentID, the complexity lies in calculating measures keeping in mind multiple enrollment from the same student, I tried to create a sample, see if it can be of any help -

Re: modelling data with several date dim

Thu, 23 Jun 2016 00:24:02 GMT

Are you using NullAsValue *; in your load script? If so, the "*" will read the null value as an empty string and include it in the returned value. If you have NullAsValue in your script remove it if you do not really need it. Otherwise you could use ">1" in place of the "*"

Re: modelling data with several date dim

Thu, 23 Jun 2016 00:31:15 GMT

I also realised that my original script was showing all the people who started in Jan and then graduated in Jan. Instead it seems that you want to know if they started, do they have a graduation date. Modify the formulas slightly to get this:

count({<PassingDate={"*"},EnrollDate={">=[Starting Date]<=[Ending Date]"}>}distinct studentID)/count({<EnrollDate={">=[Starting Date]<=[Ending Date]"}>}distinct studentID)

by changing the PassingDate to * and including the Enroll dates in the first set expression, it is saying count all those IDs that have a starting date in, say, Jan AND who have a PassingDate too.

I've included a mock model to show you what each does.