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

    Filtering / Calculating Question

    dean.knickerbocker

      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
          Matt Lutton

          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
            dean.knickerbocker

            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
              Matt Lutton

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

              • 4. Re: Filtering / Calculating Question
                dean.knickerbocker

                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
                  Matt Lutton

                  Fr.png

                  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
                    dean.knickerbocker

                    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
                      Matt Lutton

                      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!