
1. Re: Retail purchase and return
Joshua Milligan Apr 2, 2019 10:55 AM (in response to Zheng Tzer Lee)1 of 1 people found this helpfulJonathan,
A lot depends on what you want to do with the data.
Tableau Desktop
You could use the data as it is in Tableau Desktop and just write some calculations to implement logic for the returns.
If you know that a negative price indicates a return, then you could write a calculation that flags it as a return. Summing the Price will give you the correct results (100  100 + 100 = 100 with your data above). If you want to know the number of orders (not counting the original orders or the return), then you could calculate that in Tableau Desktop with something like:
COUNTD(OrderID)  (COUNTD(IF [Price] < 0 THEN[Order ID] END) * 2)
That would result in 3  (1 * 2) = 1 given your data above. You can also use calculation as the denominator (with SUM(Price) as numerator) to get the average price for orders.
So, you could use the data as it is now in Tableau Desktop and should be able to do most of your analysis.
Tableau Prep
However, if you want to remove the returns and original orders, you can do it in Prep. I've expanded on your data set to consider some complications:
I've highlighted the pairs of orders (initial order and return) that should be removed from the data set. Notice that Casing A is the same as your data set. Casing B has a couple of orders a return and then some orders between the return and subsequent reorder. Then another order of Casing B. It seems best not to assume that the order IDs will be exactly sequential (though I am assuming that you can determine the sequence of orders through the ID or maybe a date/timestamp). Casing C has a couple of returns. So we'll have to account for that.
The basic approach will be to find all the negative priced orders and then match them to the order of the same casing & same price that happened prior to the return, but closest to it. For example, we'll want to count Order #5 as the initial order for the return of Order #6. here's the flow:
The flow itself might seem a bit complicated, but I've attached it so you can dig into the details. The basic outline is:
 Split out the Orders from the Returns. From this point on, an "order" is a record with a positive price  the return records have been removed from this set of data.
 Join the Orders to the Returns to find all possible matches
 Aggregate the results to find the Max Order ID
 Join those results back to the Orders to get the orders that actually had the return
 The result here is all the orders, but ones that were returned have a Return Order ID
 You can now keep only records with a NULL Return Order ID to keep only records that were neither a return nor the initial purchase that was returned.
Hope that helps!
Joshua

2. Re: Retail purchase and return
Zheng Tzer Lee Apr 7, 2019 4:58 AM (in response to Joshua Milligan)Wow, amazing. Thanks.

3. Re: Retail purchase and return
Joshua Milligan Apr 9, 2019 7:30 AM (in response to Zheng Tzer Lee)You’re welcome! Glad to help!
Joshua