Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

time passed between 2 dates by Customer ID

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 IDOrder IDOrder DateDiff
122/17/20200
132/22/20205
1105/18/202086
253/30/20200
264/20/202021
311/2/20200
343/16/202074
375/7/202052
385/16/20209
395/17/20201
1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: time passed between 2 dates by Customer ID

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
];

View solution in original post

2 Replies
Highlighted
Specialist
Specialist

Re: time passed between 2 dates by Customer ID

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
];

View solution in original post

Highlighted
Specialist
Specialist

Re: time passed between 2 dates by Customer ID

commQV67.PNG