modelling data with several date dim

[#1]

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'

[sunny_talwar]

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

[#3]

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! 

[#4]

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?

[#5]

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

[#6]

Please provide some more sample data.

or else merge both tables.

[#7]

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

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

[Anonymous]

HI ben,

You have 2 options:

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

Regards!!

[Digvijay_Singh]

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 -

[#10]

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 "*"