3 Replies Latest reply on Apr 9, 2019 7:30 AM by Joshua Milligan

    Retail purchase and return

    Zheng Tzer Lee



      Hope someone could guide me on this.


      Facing one issue when cleaning the data:


      OrderID     Item Name     Price

      0001            Casing A       100

      0002            Casing A      -100

      0003            Casing A       100


      the above situation happen when customer buy, then forgot to ask for receipt, therefore return, and bought again.

      I couldn't just exclude the 'negative-price' 0002, as this would make it become 2 purchase of Casing A.


      How would you approach such situation?

      They don't have record for return item.





      Tableau prep: 2019.1.3

        • 1. Re: Retail purchase and return
          Joshua Milligan



          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:

          1. 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.
          2. Join the Orders to the Returns to find all possible matches
          3. Aggregate the results to find the Max Order ID
          4. Join those results back to the Orders to get the orders that actually had the return
          5. The result here is all the orders, but ones that were returned have a Return Order ID
          6. 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!


          1 of 1 people found this helpful
          • 2. Re: Retail purchase and return
            Zheng Tzer Lee

            Wow, amazing. Thanks.

            • 3. Re: Retail purchase and return
              Joshua Milligan

              You’re welcome! Glad to help!