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

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!

• ###### 1. Re: Create a calculated field that groups values and compares previous group value

Could you attach packaged workbook.

Your table sample is missing "term' and I'm afraid you still have some hidden problem.

Thanks,

Shin

• ###### 2. Re: Create a calculated field that groups values and compares previous group value

Here is the packaged workbook. I'm using Tableau 10 - sorry! first time posting.

Also, here is the correct example table I'd like to produce with the calculated field, "enrollment_category":

 term ayid enroll_status enrollment_category Fall 20082009 matriculated matriculated Spring 20082009 matriculated matriculated Spring 20082009 matriculated matriculated Fall 20092010 enrolledft year 1 Spring 20092010 enrolledft Spring 20092010 enrolledft Fall 20102011 enrolledft year 2 Spring 20102011 enrolledft Spring 20102011 enrolledft Fall 20112012 enrolledft year 3 Spring 20112012 enrolledft Spring 20112012 enrolledft Spring 20112012 graduated
• ###### 3. Re: Create a calculated field that groups values and compares previous group value

Hi Joanne

Does this help?

[The Yr in table]

[Ayid]%10000

[The first Yr in table]

The first Yr in table

[Category]

IF [Enroll Status]='matriculated'

THEN 'matriculated'

ELSEIF [Enroll Status]='enrolledft' AND [Term]='Fall'

then "Year "+str([Ayid]%10000 -[The first Yr in table])

end

Copy Ayid

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Create a calculated field that groups values and compares previous group value

Hi Shin,

Thanks for that! I tried to replicate what you did and realized I didn't include a couple of additional parameters: the years should start over again based upon student ID and if a student has an enroll_status as graduated in the same year, it should tag the year and that the student graduated.  So the overall table should look like:

 studentrecordid term ayid enroll_status enrollment_category 1 Fall 20082009 matriculated matriculated 1 Spring 20082009 matriculated matriculated 1 Spring 20082009 matriculated matriculated 1 Fall 20092010 enrolledft Year 1 1 Spring 20092010 enrolledft 1 Spring 20092010 enrolledft 1 Fall 20102011 enrolledft Year 2 1 Spring 20102011 enrolledft 1 Spring 20102011 enrolledft 1 Fall 20112012 enrolledft 1 Spring 20112012 enrolledft 1 Spring 20112012 enrolledft 1 Spring 20112012 graduated Year 3 - Graduated 2 Fall 20042005 matriculated matriculated 2 Spring 20042005 matriculated matriculated 2 Spring 20042005 matriculated matriculated 2 Fall 20052006 enrolledft Year 1 2 Spring 20052006 enrolledpt 2 Spring 20052006 enrolledft 2 Fall 20062007 enrolledft Year 2 2 Spring 20062007 enrolledft 2 Summer 20062007 enrolledft 2 Fall 20072008 enrolledft Year 3 2 Spring 20072008 enrolledpt 2 Spring 20072008 enrolledft 2 Summer 20072008 enrolledft 2 Fall 20082009 enrolledft 2 Spring 20082009 graduated Year 4 - Graduated 2 Spring 20082009 enrolledpt 2 Spring 20082009 enrolledft

When I pull in student id with your code, I see the following:

I edited some code and got the output to look like:

I updated the category variable to be:

IF [Enroll Status]='matriculated'

THEN 'matriculated'

THEN "Year "+str([Ayid]%10000 -[The first Yr in table])+" graduated"

ELSEIF [Enroll Status]='enrolledft' AND [Term]='Fall'

then "Year "+str([Ayid]%10000 -[The first Yr in table])

end

and the first yr in table to be:

{fixed [Studentrecordid]:min([The Yr in table])}

This is probably a super simple question, but how to remove the "year 5" when the cell already has "year 5 graduated" in it?

(Note: I started another discussion here Group by mutiple paramaters in a calculated field for questions related to creating other calcuated fields that try to group by additional parameters not mentioned in this discussion)

• ###### 5. Re: Create a calculated field that groups values and compares previous group value

Joanne,

It's causing the issue if your formula includes "term" but the table does not have "term".

I don't know your expectation regarding how to handle "term".

IF [Enroll Status]='matriculated'

THEN 'matriculated'

THEN "Year "+str([Ayid]%10000 -[The first Yr in table])+" graduated"

ELSEIF [Enroll Status]='enrolledft' AND [Term]='Fall'

then "Year "+str([Ayid]%10000 -[The first Yr in table])

end

Thanks,

Shin

• ###### 6. Re: Create a calculated field that groups values and compares previous group value

Thanks, Shin, for your help on this!

I got it to do what I wanted with your help and did see that term was the variable causing the double tagging. I'm going to think a little more on how we can handle this aspect, but this got to me to a great start!

• ###### 7. Re: Create a calculated field that groups values and compares previous group value

One thing.

If you think appropriate, to close the thread, could you mark my answer as correct.

Thanks,

Shin