7 Replies Latest reply on Dec 13, 2016 11:24 AM by Shinichiro Murakami

    Create a calculated field that groups values and compares previous group value

    Joanne Lacsina

      Hello! I’m working with a long dataset and I’m trying to create a calculated field (enrollment_category) that will tag rows based upon multiple criteria of academic year and enrollment status. The output I’m looking for would be similar to the below.

       

      ayid

      enroll_status

      enrollment_category

      20072008

      enrolledpt

      20082009

      matriculated

      matriuclated

      20082009

      matriculated

      matriuclated

      20082009

      matriculated

      matriuclated

      20092010

      enrolledft

      year1

      20092010

      enrolledft

      year1

      20102011

      enrolledft

      year2

      20102011

      enrolledft

      year2

      20112012

      enrolledft

      year3

      20112012

      enrolledft

      year3

      20122013

      graduated

       

      The code I’m trying, but doesn’t work is:

       

      IF [enroll_satus]='matriculated' THEN 'matriculated' ELSEIF

      ([enroll_satus]='enrolledft' AND [term]='Fall' AND

      [ayid]=(LOOKUP(ATTR([ayid]),-1)+10001) THEN 'Year 1' ELSEIF

      ([enroll_satus]='enrolledft' AND [term]='Fall' AND

      [ayid]=(LOOKUP(ATTR([ayid]),-2)+20002) THEN 'Year 2' ELSEIF

      ([enroll_satus]='enrolledft' AND [term]='Fall' AND

      [ayid]=(LOOKUP(ATTR([ayid]),-3)+30003) THEN 'Year 3' ELSEIF

      ([enroll_satus]='enrolledft' AND [term]='Fall' AND

      [ayid]=(LOOKUP(ATTR([ayid]),-4)+40004) THEN 'Year 4'

      END

       

        would appreciate any advice. Thank you!