Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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.