5 Replies Latest reply on Jul 3, 2017 1:49 PM by Alex Martino

# Link refunds to original month

I have transactions IDs that can be either order or refund (for "type"). The dates are when the nistance occurs. How can I tie the refunds back to the date of the associated order transaction? Thanks.

• ###### 1. Re: Link refunds to original month

It all depends what you data looks like? Can you post an anonymous  sample data, it will go a long way in helping come up with a solution

• ###### 2. Re: Link refunds to original month

IDs that can be either order or refund (for "type"). The dates are when the nistance occurs. How can I tie the refunds back to the date of the associated order transaction? Thanks.

Assuming [Type] has only values "Refund" and "Order" and the order of occurrences for each OrderID is only from Order-to-Refund, where there exists refund.

Since Order comes first alphabetically, when we use MIN('Order','Refund') = 'Order' and MAX = 'Refund'

First flag Order with Refurn: If true 1 else 0. If the flag is 1 then get OrderID min order transaction date which is the first time the order was made.

```IF {FIXED [OrderId]:MAX(IF MAX([Type])='Refund' THEN 1 ELSE 0)} = 1 THEN {FIXED [OrderId]: MIN([OrderTransaction])} END
```
• ###### 3. Re: Link refunds to original month

Here is the spreadsheet. In the data source, you can see the date time associated, so any time there is a type of refund the date should be changed to match the original transaction date. Thanks.

Also, in the sheet I have, how can I visualize the refund dates with relation to original date? Could I calculate an average refund days away from original transaction?

• ###### 4. Re: Link refunds to original month

Prayson Wilfred Daniel
Thanks. Type has more values than Refund and Order. I'm not sure if the order of occurrence for each order ID is from Order to Refund (how can I use tableau to check that?). Actually, it should be since the data set is sorted by date. How does this change your answer?

I'll test your other response on a dummy response to see if it makes sense to me. Thansk!~~!

• ###### 5. Re: Link refunds to original month

It also seems like your formula is returning errors Prayson Wilfred Daniel. Spreadsheet attached. It's titled THE CALCULATION.

Could you explain the logic of that formula?