1 Reply Latest reply on Dec 19, 2016 3:29 PM by diego.medrano

    Group by mutiple paramaters in a calculated field

    Joanne Lacsina

      Hello,

       

      I’m working with a long dataset and I’m trying to create a calculated field ("continuous_enrollment" and "retained") that will tag rows (0 or 1) based upon multiple criteria of academic year, enrollment status, enrollment category, and postsecondary institution type (psi_type). I'm struggling to find a function that allows me to group by values in a specific field and compare to the next subset of values in the same field.

       

      - continuous enrollment is based upon first academic year a student enrolled in school and that their enrollment category (year 1, year 2, etc.) follow over the subsequent academic years.

      - retained is based upon the same parameters as continuous enrollment AND the psi_type remains the same at least once in any given academic year

       

      Input looks like:

       

       

       

      studentrecordidtermayidenroll_statusenrollment_categorypsi_type
      1Fall20082009matriculatedmatriculatedUC
      1Spring20082009matriculatedmatriculatedUC
      1Spring20082009matriculatedmatriculatedUC
      1Fall20092010enrolledftYear 1UC
      1Fall20092010enrolledftYear 1Community College
      1Fall20102011enrolledftYear 2UC
      1Spring20112012graduatedgraduatedCSU
      2Fall20042005matriculatedmatriculatedCSU
      2Spring20042005matriculatedmatriculatedCommunity College
      2Spring20042005matriculatedmatriculatedCSU
      2Fall20052006enrolledftYear 1CSU
      2Fall20062007enrolledftYear 2CSU
      2Fall20072008enrolledftYear 3CSU
      2Spring20082009graduatedGraduatedCSU
      2Spring20082009enrolledptCSU
      2Spring20082009enrolledftCSU
      3Fall20082009matriculatedmatriculatedCommunity College
      3Spring20082009matriculatedmatriculatedCommunity College
      3Spring20082009matriculatedmatriculatedCommunity College
      3Fall20102011enrolledftYear 1Community College
      3Fall20112012enrolledftYear 2Community College
      3Fall20122013enrolledftYear 3CSU
      3Spring20122013enrolledftgraduatedCSU

                                                                                                                                                                                                                                                                                      

       

       

      I would like output to look like:

       

       

               

      studentrecordidtermayidenroll_statusenrollment_categorypsi_typecontinuous_enrollmentretained
      1Fall20082009matriculatedmatriculatedUC11
      1Spring20082009matriculatedmatriculatedUC11
      1Spring20082009matriculatedmatriculatedUC11
      1Fall20092010enrolledftYear 1UC11
      1Fall20092010enrolledftYear 1Community College11
      1Fall20102011enrolledftYear 2UC11
      1Spring20112012graduatedgraduatedCSU10
      2Fall20042005matriculatedmatriculatedCSU11
      2Spring20042005matriculatedmatriculatedCommunity College11
      2Spring20042005matriculatedmatriculatedCSU11
      2Fall20052006enrolledftYear 1CSU11
      2Fall20062007enrolledftYear 2CSU11
      2Fall20072008enrolledftYear 3CSU11
      2Spring20082009graduatedGraduatedCSU11
      2Spring20082009enrolledptCSU11
      2Spring20082009enrolledftCSU11
      3Fall20082009matriculatedmatriculatedCommunity College01
      3Spring20082009matriculatedmatriculatedCommunity College01
      3Spring20082009matriculatedmatriculatedCommunity College01
      3Fall20102011enrolledftYear 1Community College01
      3Fall20112012enrolledftYear 2Community College01
      3Fall20122013enrolledftYear 3CSU00
      3Spring20122013enrolledftgraduatedCSU00

                                                                                                                                                                                                                                                                                                                                                                        

      I working with tableau 10. Any help would be much appreciated.