Nicholas, this looks doable. Please post a packaged workbook with some sample data, so we can put it together for you. Thanks,
Nicholas, what you need is a "percent difference from previous" table calculation computed over year. For example:
Retention = (COUNT([SID])) / LOOKUP((COUNT([SID])), -1)
(depending on your actually data; COUNTD might be necessary)
Tableau can do this one for you automatically as a Quick Table Calculation.
One thing to consider though is that this is a fairly naive retention measure. Namely, it doesn't account for 100% turnover of students in a program from year to year. For example, if PROG1 in YEAR1 is SID1 and SID2, but it YEAR2 it has SID3 and SID4, this will end up as 100% retention, But the program didn't really retain any students; it just replaced them. To account for replacement, you'd probably need to check the each student to see if they were in the program the previous year and then divide the number of those students by the total number of students in the program the previous year. That's a trickier calculation.
I've attached a workbook that gives a basic solution to your problem.
Retention.twbx.zip 13.6 KB
I need help with the trickier calculation. I have a similar dataset but in weekly increments. I need a calculation that counts unique customers at the start (CS), and unique customers at the end of the next week (CE) and unique new customers i.e. at the end but not in the previous week (CN). The calculation should be (CE-CN)/CS)x100=Retention. I have found similar attempts but nothing has worked so far. Any Ideas?
try creating formulas such as this:
S2012 : (if year = 2012 then [SID] else null end) //Students for 2012
S2013 : (if year = 2013 then [SID] else null end) //Students for 2013
difference : countd(if S2012 is not null and S2013 is null then [SID] else null end) //Students in 2012 but not 2013
Hope this helps.