Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am building a student retention summary table. The data structure is like below, it shows the student ID, starting year and registered year (please assume the data structure is like this and cannot change)
StudentID | Starting Year | Registered Year |
---|---|---|
1 | 2003 | 2003 |
1 | 2003 | 2004 |
1 | 2003 | 2006 |
1 | 2003 | 2007 |
2 | 2003 | 2003 |
2 | 2003 | 2005 |
2 | 2003 | 2006 |
2 | 2003 | 2007 |
3 | 2003 | 2003 |
3 | 2003 | 2004 |
3 | 2003 | 2005 |
3 | 2003 | 2006 |
4 | 2004 | 2004 |
4 | 2004 | 2005 |
4 | 2004 | 2006 |
5 | 2004 | 2004 |
5 | 2004 | 2006 |
5 | 2004 | 2007 |
So what I need is to build a table summarize the retention of each starting year.
Starting Year | Enrollment | Retained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc.) |
---|---|---|
2003 | 3 (count (studentID)) | 2 (has problem) |
2004 | 2 (count (studentID)) | 1 (has problem) |
In this case, I have problem calculating retained students, I tried below formulas:
1. If([registered year]=[starting year]+1, count(studentID))
It doesn't work. I check the formula, it seems this logic ([registered year]=[starting year]+1) is always false.
2. count({$< [Registered year]={$(=[starting year]+1)}, [starting year]=>} studentID)
It doesn't work. The results are 0. Because I didn't make any selection on [Starting year], if I made selection on starting year , it will be calculated correctly. But I don't want to make any selections as it is a summary table.
So I tried another way by adding a Retained Year column, Retained year is Registered Year = Starting Year +1 (this 1 could change, it could be 2,3,4 years.)
Starting Year | Retained Year | Enrollment | Retained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc..) |
---|---|---|---|
2003 | 2004 | 3 (has problem) | 2 (count (studentID)) |
2004 | 2005 | 2 (has problem) | 1 (count (studentID)) |
in which Retained Year = Starting Year +1
So now the problem is the Enrollment.
I tried this:
count ( total<[starting year]> studentID)
or
count ({$< [Registered year]=>} total<[starting year]> studentID)
or
count ({$< [Registered year]=>} studentID)
None of them work. the results are the same as Retained Students.
Is there anyone could help please? Any suggestions or guidance are welcome. i've been trying for a couple of days but no luck yet!
Regards,
Anni
A bit of update:
If I expand the retained year
Starting Year | Retained Year | Enrollment | Retained Students |
---|---|---|---|
2003 | 2004 | 3 | 2 (count (studentID)) |
2003 | 2005 | 3 | 2 |
2003 | 2006 | 3 | 3 |
2003 | 2007 | 3 | 2 |
Then I can use
Enrollment: count ( total<[starting year]> studentID)
However, I still want to show 1 year retention only, which means the retained year = starting year + 1. In this case, the enrollment formula is not working..
Any other suggestions?