10 Replies Latest reply on Oct 3, 2018 1:24 PM by Deepak Rai

# Calculate difference in values between two rows

New to Tableau so forgive me if this is an easy one:

I want to calculate gains in enrollment from one year to the next, with the data in long format.

School     Year     Enrollment

St. James     2016     200

St. James     2017     210

St. James     2018     220

I want to do something like:

Enrollment 2017 = Enrollment WHEN Year=2017

Enrollment 2018 = Enrollment WHEN Year=2018

Gain = Enrollment 2018 - Enrollment 2017

How to calculate this simple gain from 210 to 220?

Thanks for any help you can provide.

Jack

• ###### 1. Re: Calculate difference in values between two rows

If you have your data partitioned on your sheet by year, then LOOKUP(SUM([Enrollment]), -1) will get you the prior year's enrollment.

You can also create separate calcs like you proposed using LOD:

{ FIXED [School] : SUM( IF YEAR = 2018 then [Enrollment] END) }

But that will require you to have separate calcs for each year.

• ###### 2. Re: Calculate difference in values between two rows

This is your Calculation for Difference

SUM({FIXED [School]:SUM(IF Year="2017" THEN ([Enrollment]) END)})-SUM({FIXED [School]:SUM(IF Year="2018" THEN ([Enrollment]) END)})

Thanks

Deepak

• ###### 3. Re: Calculate difference in values between two rows

This worked.  Thanks, gentlemen!

1 of 1 people found this helpful
• ###### 4. Re: Calculate difference in values between two rows

It is Great to know that it Worked, But which Calculation was Helpful to You?

• ###### 5. Re: Calculate difference in values between two rows

Deepak -- yours puts it all nicely into one calc, so I assume he did that.

Problem with it is that in a few months those hard-coded values will need to be changed when we get to 2019.

I'm going to mark yours as the correct answer.

• ###### 6. Re: Calculate difference in values between two rows

Yes True Joe...btw..I think you will be at TC 18?

• ###### 7. Re: Calculate difference in values between two rows

I absolutely will.  I look forward to meeting you!

• ###### 8. Re: Calculate difference in values between two rows

Definitely Sir!!! Looking forward to meet the person who answered my first post in  this Community

• ###### 9. Re: Calculate difference in values between two rows

I used Joe's LOD expression to do the calc for each year, then calculated a few field for the gain.  I agree it's only a temporary solution for this year but it's a good simple LOD for me starting out.  I can see how yours (Deepak) does the whole thing in one field.  Thanks again to both of you, gentlemen.

• ###### 10. Re: Calculate difference in values between two rows

You are Welcome!!!