This can be done by creating a calculated field similar to the following:
if max(Timestamp)=window_max(max(Timestamp)) and ATTR([Sheet1 (PIPELINE DATA.xlsx)].[STATUS])='STOCK' and attr(Type)='DELIVERY'
elseif max(Timestamp)=window_max(max(Timestamp)) and ATTR([Sheet1 (PIPELINE DATA.xlsx)].[STATUS])='SOLD' and attr(Type)='RETURN'
Then, place this on the view. Right click on it and select Edit Table Calculation... Choose Compute Using-->Advanced. Place Order #, Day of Timestamp and Type in the Compute Using box (keep that order). Click OK, and in At the level-->Deepest, Restarting Every-->Order #.
Hope this helps!
Thank you Tracy. It works. But I would like to have the total sales summary by party. How it can be done?
Take a look at the attached, it uses some techniques detailed by Jonathan Drummey at http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/
You will also want to evaluate your current business logic, and ensure it aligns with your data logic. There could be a conflict if one date has multiple Types for a single Order.
The same result can be reached more efficiently with a formula like:
IF LAST()=0 AND ATTR([Type])="DELIVERY" AND [Pipeline Status]="STOCK" THEN 1
ELSEIF LAST()=0 AND ATTR([Type])="RETURN" AND [Pipeline Status]="SOLD" THEN -1
ELSE 0 END
and an advanced compute using on just Timestamp and Type (with Timestamp at the top of the list).
The Automatic setting will then sort according to the value of Timestamp ascending, making LAST()==0 the final value. This would help address the ambiguous situation of multiple Type values for a single Timestamp Order combination. In addition to less clicks to setup, I believe Tableau could evaluate this route faster, especially when dealing with more records.
Ah, good point Joe, thanks for sharing!