Interval between dates

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

Good afternoon!

I have a problem and I do not know how to solve

I need to show how many customers returned to the store in less than 24/48/72 hours.

I have the following table:

ID	DATE	CUSTOMERS
6223875	03/02/2018 19:18:37	1050943
6223876	31/01/2018 19:18:37	1050943
6223874	24/01/2018 19:18:37	1050943
6220698	24/01/2018 07:46:19	1050943
6220443	23/01/2018 03:41:50	1050943
5813877	21/01/2018 02:58:47	1050943

If I select the date 03/02/2018 I need that my client appears sorted with the return in 72 hours

If I select the date of 01/24/2018 I need my client to appear ranked with the return in 24 hours between 23/01/2018 and 01/24/2018

If I select the date 23/01/2018 I need my client to appear ranked with the return in 48 hours

If I select the date of 01/21/2018 my client will not be considered

How can I do this?

I've already tried using Peek, Below, above, interval, and i tried to create a table on script.

Thanks

Re: Interval between dates

Nicole-Smith — Mon, 05 Feb 2018 19:49:00 GMT

Load script like this should do the trick:

Temp:
LOAD ID, 
 DATE#(DATE, 'DD/MM/YYY hh:mm:ss') AS DATE, 
 CUSTOMERS
INLINE [
 ID, DATE, CUSTOMERS
 6223875, 03/02/2018 19:18:37, 1050943
 6223876, 31/01/2018 19:18:37, 1050943
 6223874, 24/01/2018 19:18:37, 1050943
 6220698, 24/01/2018 07:46:19, 1050943
 6220443, 23/01/2018 03:41:50, 1050943
 5813877, 21/01/2018 02:58:47, 1050943
];


Final:
LOAD ID,
 DATE,
 CUSTOMERS,
 IF(DIFF <= 1, 24, IF(DIFF <= 2, 48, IF(DIFF <= 3, 72))) AS RETURN_WITHIN;
LOAD ID, 
 DATE, 
 CUSTOMERS, 
 IF(CUSTOMERS = PREVIOUS(CUSTOMERS), DATE#(DATE, 'DD/MM/YYY hh:mm:ss') - DATE#(PREVIOUS(DATE), 'DD/MM/YYY hh:mm:ss')) AS DIFF
RESIDENT Temp
ORDER BY CUSTOMERS, DATE;


DROP TABLE Temp;

It will return a table that looks like this:

ID	DATE	CUSTOMERS	RETURN_WITHIN
5813877	1/21/2018 2:58:47 AM	1050943
6220443	1/23/2018 3:41:50 AM	1050943	72
6220698	1/24/2018 7:46:19 AM	1050943	48
6223874	1/24/2018 7:18:37 PM	1050943	24
6223876	1/31/2018 7:18:37 PM	1050943
6223875	2/3/2018 7:18:37 PM	1050943	72

topic Interval between dates in QlikView

Interval between dates

Re: Interval between dates