Duration between order status changes

theotshirt — Mon, 30 Jan 2023 13:13:57 GMT

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.

Re: Duration between order status changes

edwin — Mon, 30 Jan 2023 19:54:56 GMT

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.

topic Re: Duration between order status changes in App Development

Duration between order status changes

Re: Duration between order status changes