7 Replies Latest reply on Mar 26, 2014 9:01 AM by Matt Lutton

# Filtering / Calculating Question

Hi All

I have data for a manufacturing process in which a set of tests are done to measure levels as each product moves further along the process.  I need to do a calculation to determine the difference in levels between the first test and the last test.  My issue is that the number of tests performed is dynamic.  It is usually 3-4 tests per product.  Sometimes it may be only 2, but it can go up to as high 10.

My thought would be to do a simple table calculation between the first and last test, if I could somehow filter on this.  But I am not sure I can set up Tableau to understand what the last is.

The data is stored in a table with a format similar to:

Product ID, Test Number, Measure Level

Anyone see a way to determine this within Tableau?  Maybe I am just missing something obvious (or not so obvious).

Thank you and Cheers!!

• ###### 1. Re: Filtering / Calculating Question

Just about anything is possible, and we have functions such as FIRST() and LAST() that can be used to refer to specific records.  I think it would be best to mock up a sample packaged workbook, set up with a view the way you want it, and explain the goal for the view or provide a mock up of your intended results.  Its just very difficult for me to visualize what you are hoping to accomplish in my head.

• ###### 2. Re: Re: Filtering / Calculating Question

Hi Matthew - Was just about to post a packaged workbook and saw your response.

In this sample, I am essentially trying to dynamically determine the last test for each product and then calculate the difference between it and the first test:

Product 1 - Value difference between tests 3 and 1

Product 2 - Value difference between tests 4 and 1

Product 3 - Value difference between tests 2 and 1

Product 4 - Value difference between tests 4 and 1

Product 5 - Value difference between tests 3 and 1

• ###### 3. Re: Re: Filtering / Calculating Question

Do you only want the First and Last test for each product displayed in the view?

• ###### 4. Re: Filtering / Calculating Question

Yes that would be perfect.  I have set up Table Calculations between each subsequent test and the first, but wasn't quite sure how to filter the intermediary values which aren't needed.   Displaying the first and last value would be ideal.  Thanks!!

• ###### 5. Re: Filtering / Calculating Question

I operated with the assumption you only want to display the first and last test numbers for each Product.

In the attached, I have created a Filter to Keep only the First and Last values.  That calc is simply:

FIRST()==0 OR LAST()==0, with an advanced compute using of addressing on Product, then Test Number, at the Level of Test Number, restarting every Product.

The Difference calc was created by editing the SUM(Value) pill as a Quick Table Calculation of "Difference" (accessible by right clicking on the pill).  Once we create the quick table calc, Tableau creates a calculation for us, but that calculation isn't automatically added as a field in our Data Window.  So, we choose to "Edit Table Calculation", then "Customize" so we can explicitly define a name for the Field.

The calculation is automatically generated, but I modified it to always refer to the FIRST() value, rather than the standard -1 value which would calculate the difference from previous.  You can see this by viewing the calculated field "Difference from First Test".  This calculation has a similar compute using of: Product, then Test Number, restarting every Product (see screenshot above).

I hope this helps!

• ###### 6. Re: Filtering / Calculating Question

Brilliant!!  Thank you Matthew.  Now I just need to work it into my actual workbook with a far more complex data source.  But I appreciate the amazingly fast help!

• ###### 7. Re: Filtering / Calculating Question

No problem.  If you don't have a lot of experience with these types of calculations, I'm happy to try and help where I can--feel free to reach out via email if you need to.  No guarantees, but I'll help if I can.  Cheers!