Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

modelling data with several date dim

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'

10 Replies
Not applicable
Author

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.