topic time passed between 2 dates by Customer ID in QlikView

time passed between 2 dates by Customer ID

andreip21 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

i want to calculate in script, by every Order ID of the following 3 customers the time difference (in days) from their previous order.

In case of their first order, the Diff should be 0.

for ex, for Customer ID 1, his second order (22.02) will have on the desired calculated column 5, because his first order was 5 days ago (17.02).

P.S. i don't have the table sorted like in this example.

Customer ID	Order ID	Order Date	Diff
1	2	2/17/2020	0
1	3	2/22/2020	5
1	10	5/18/2020	86
2	5	3/30/2020	0
2	6	4/20/2020	21
3	1	1/2/2020	0
3	4	3/16/2020	74
3	7	5/7/2020	52
3	8	5/16/2020	9
3	9	5/17/2020	1

Re: time passed between 2 dates by Customer ID

Saravanan_Desingh — Sat, 23 May 2020 12:11:34 GMT

One solution is.

tab1: LOAD *, If([Customer ID]=Previous([Customer ID]),[Order Date]-Peek([Order Date]),0) As Diff; LOAD * INLINE [ Customer ID, Order ID, Order Date 1, 2, 2/17/2020 1, 3, 2/22/2020 1, 10, 5/18/2020 2, 5, 3/30/2020 2, 6, 4/20/2020 3, 1, 1/2/2020 3, 4, 3/16/2020 3, 7, 5/7/2020 3, 8, 5/16/2020 3, 9, 5/17/2020 ];

Re: time passed between 2 dates by Customer ID

Saravanan_Desingh — Sat, 23 May 2020 12:12:38 GMT