1 Reply Latest reply: Dec 19, 2016 12:55 AM by anni zhang RSS

    Retention Summary in Pivot table: Ignore one dimension or Use a Dimension in Set analysis

    anni zhang

      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
      120032003
      120032004
      120032006
      120032007
      220032003
      220032005
      220032006
      220032007
      320032003
      320032004
      320032005
      320032006
      420042004
      420042005
      420042006
      520042004
      520042006
      520042007

         

       

      So what I need is to build a table summarize the retention of each starting year.

       

      Starting YearEnrollmentRetained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc.)
      20033 (count (studentID))

      2 (has problem)

      20042 (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

      EnrollmentRetained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc..)
      200320043 (has problem)

      2 (count (studentID))

      200420052 (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