Figuring out the time between 1st and 2nd order

Question

I have a transactional log with one row representing one order. Customers can have multiple orders, or just a single one. I want to figure out the time difference between customers making their first order and 2nd order, ignoring the 3rd/4th/5th etc orders. Some customers have a single order so wouldn't apply, some have like 25 spread over 3 years.  I'm really struggling to figure this out, especially as Alteryx seems to be sorting my dates weirdly, despite me ensuring the order date column is recognised as datetime format.

Current data:

Customer IDOrder Date1000003930/05/20231000003901/06/20231000003908/06/20231000008520/11/20231000008502/04/2023

The goal:

Customer IDDays between 1st and 2nd transaction10000039910000085232

1st and 2nd order - sample data.csv

Raj · Answer

@danilang 
As per my understanding from the output data attached, he is trying to calculate the difference between 1st and last order.

Regards,

Raj

Raj · Answer

@SophieRawcliffe 
please find the workflow attached
mark done if solved.

1162.yxmd

danilang · Answer

Hi @SophieRawcliffe

What do you mean by Alteryx is sorting your dates weirdly?  If your dates are in YYYY-MM-DD and you're sorting by CustomerID and Date, the difference between the 1st and 2nd record for each customer will give you what you're looking for.

BTW for customer 10000039, the difference between the 1st and 2nd order is only 3 days(2 if you're exclusive of the first date). May 30-Jun 01

Dan