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.
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
Sample Test Data.twbx 17.8 KB
Do you only want the First and Last test for each product displayed in the view?
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!!
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!
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!
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!