4 Replies Latest reply on May 22, 2014 10:40 AM by Robert Sutter

    Quick and Easy Retention Rates?

    nicholas.richter

      Hi All,

       

      This is actually a question that could be generalized beyond education, but I am trying to figure out how to best incorporate retention rates into a tableau worksheet over time. My goal is to get to this:

       

      Year 1Year 2Year 3
      50%60%70%

       

      Where each of the percentages are calculated on the fly based on a filter applied and represent the percentage of students from YEAR-1 also enrolled in YEAR (So, 50% of Students from Year 0 were also enrolled in Year 1). Currently, I have a medium sized data set that looks something like this:

       

      SIDProgramEnrolledYear
      SID1PROG1YEAR1
      SID2PROG1YEAR1
      SID1PROG1YEAR2
      SID1PROG1YEAR3
      SID3PROG2YEAR3
      SID3PROG2YEAR4

       

      I would like to take this and find a way that I could filter by PROG1 and get:

      Year  2Year 3Year 4
      50%100%N/A

       

      And then subsequently filter by PROG2 in a dashboard and get the following without major hassle:

      Year  2Year 3Year 4
      N/AN/A100%

       

      Any ideas on how best to do this?

        • 1. Re: Quick and Easy Retention Rates?
          Shawn Wallwork

          Nicholas, this looks doable. Please post a packaged workbook with some sample data, so we can put it together for you. Thanks,

           

          --Shawn

          • 2. Re: Quick and Easy Retention Rates?
            Zac Hilbert

            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.

             

            Zac

            • 3. Re: Quick and Easy Retention Rates?
              Travis Eck

              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?

              • 4. Re: Quick and Easy Retention Rates?
                Robert Sutter

                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.

                 

                Robb