10 Replies Latest reply on Apr 4, 2017 9:49 AM by Shinichiro Murakami

# Difference based on previous row value for unique

I want to calculate Oil change interval for each vehicle identified by veh_no. In Excel I use formula

=IF(A2=A1,C2-C1,C2)

Column D is the one with Oil change interval calculation done by above formula in excel. I want to create this calculation like Column D in Tableau ? I have attached the data.

Thank you

Shailesh

• ###### 1. Re: Difference based on previous row value for unique

You can do this by using a table calculation. That way you don't have to use an "IF" Statement. If you set it up correctly it'll handle the "IF" for you by only performing the calculation on the same ID. Here is a simple example. See the picture below and the attached workbook (I used super store sales data to create this example):

I hope this helps!

--Matt

1 of 1 people found this helpful
• ###### 2. Re: Difference based on previous row value for unique

Hi Matt,

That helped a lot. Thanks for quick response.

1 of 1 people found this helpful
• ###### 3. Re: Difference based on previous row value for unique

In your case, you can create calculated field.

[Delta calc]

ZN(SUM([Avg. Vehicle Miles@Failure])) - LOOKUP(ZN(SUM([Avg. Vehicle Miles@Failure])), -1)

With default tale calc at version 9.3, I cold not set re-start criteria.

Thanks,

Shin

• ###### 4. Re: Difference based on previous row value for unique

In the above example, if it's first value or only value unique order ID, I want to replace it by that value and not by "Null" ? How do I do that ? Like for e.g. the one circled shows difference as Null but want to show as \$378, \$91 & so on.

• ###### 5. Re: Difference based on previous row value for unique

Hi Shinichiro,

Thank you for your reply. That also worked well except I don't see the field 'Restart every' in Tableau 10.0

• ###### 6. Re: Difference based on previous row value for unique

Hi Shailesh

That's not version dependency.  As I said, built-in table calc does not brings it.

So I needed to crate another calculated field.

Thanks,

Shin

• ###### 7. Re: Difference based on previous row value for unique

Shini,

That's very good tip. Thanks ! I was able to replicate it by creating new field Delta_Oil as shown.  I still don't how to account for first Oil change. For e.g. for vehicle 327790, first oil change was at 41,007 miles. Since this is the first Oil change, I want to show Oil change interval as 41,007. Currently it shows null. Is there way to do this ?

• ###### 8. Re: Difference based on previous row value for unique

Shailesh,

Here you go.

[Delta calc]

if first()=0 then sum([Avg. Vehicle Miles@Failure])

else ZN(SUM([Avg. Vehicle Miles@Failure])) - LOOKUP(ZN(SUM([Avg. Vehicle Miles@Failure])), -1) end

Thanks,

Shin

• ###### 9. Re: Difference based on previous row value for unique

Shin,

That worked well.  Thank you so much !  Appreciate all the help.

Shailesh

• ###### 10. Re: Difference based on previous row value for unique

Shailesh

You are welcome.

Shin