Duration between order status changes

[theotshirt]

Hello, 

We have some data about orders : order date , status, status change date, carrier

We would like to calculate the duration between some status. For exemple, "the percentage of order whose duration between created and in_delivery = 3)

I trie to created an specific date for each status but it doesn't work : 

LOAD
numéro commande,
status,

date de passage status, 

date(floor(date de passage status)) as date_passage_in_delivery
where status='in_delivery'; 

Do you have any ideas ? 

Many thanks in advance!

Here is the data : 

Date commande	Numéro commande	Status	date de passage status	Transporteur
22/01/2023	Commande 1	created	22/01/2023	1
22/01/2023	Commande 1	in_production	22/01/2023	1
22/01/2023	Commande 1	in_delivery	22/01/2023	1
22/01/2023	Commande 1	delivered	23/01/2023	1
22/01/2023	Commande 2	created	22/01/2023	2
22/01/2023	Commande 2	in_production	22/01/2023	2
22/01/2023	Commande 3	created	22/01/2023	1
23/01/2023	Commande 4	created	23/01/2023	1
23/01/2023	Commande 4	in_production	23/01/2023	1
23/01/2023	Commande 4	in_delivery	24/01/2023	1
23/01/2023	Commande 4	delivered	26/01/2023	1
23/01/2023	Commande 5	created	23/01/2023	2
23/01/2023	Commande 5	in_production	25/01/2023	2
23/01/2023	Commande 5	in_delivery	25/01/2023	2
22/01/2023	Commande 6	created	22/01/2023	1
22/01/2023	Commande 6	in_production	25/01/2023	1
22/01/2023	Commande 6	in_delivery	26/01/2023	1

And what we would like to get : 

Best regards.

[edwin]

you can create a new column called DURATION which you can compute at load time.  then you can use that as a modifier in your set analysis.

{<Duration={"<=3"}>}

this set analysis modifier will then pull all durations <= 3.