Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

osnap822
New Contributor II

QlikSense Check for 30 days date of records from sub-group

Good Day! I need to check and identify records where the dates is within 30 days range comparing the dates of all other records that belongs to the group. I have below table to explain it further. (sorry if this is confusing)

So basically using the VENDOR field, I check all records that have similar value (to group the records) and then compare each dates and identify which are within 30 days range of each other. Please help advise if this is possible?

(i just use different font color to show the grouping based on same vendor values)

TABLE:

DEPARTMENT             VENDOR                         DATE (mm/dd/yyyy)

A                                    SUPPLIER-A                    4/5/2016

B                                    SUPPLIER-A                    3/22/2016

C                                    SUPPLIER-A                    10/18/2016 

D                                    SUPPLIER-B                    6/20/2016

E                                    SUPPLIER-B                    7/10/2016

F                                    SUPPLIER-B                    8/5/2016

G                                   SUPPLIER-B                     9/2/2016

H                                   SUPPLIER-B                     11/15/2016

I                                     SUPPLIER-C                    5/26/2016

J                                    SUPPLIER-C                    5/5/2017



EXPECTED RESULT:

I was thinking if it's possible to create another dimension during data load script to identify the 30 days range check?

(i just use different font color to show the grouping based on same vendor values)


DEPARTMENT             VENDOR                         DATE (mm/dd/yyyy)           30 DAYS CHECK?

A                                    SUPPLIER-A                    4/5/2016                                   YES

B                                    SUPPLIER-A                    3/22/2016                                 YES

C                                    SUPPLIER-A                    10/18/2016                               NO

D                                    SUPPLIER-B                    6/20/2016                                 YES

E                                     SUPPLIER-B                    7/10/2016                                YES

F                                     SUPPLIER-B                    8/5/2016                                  YES

G                                    SUPPLIER-B                     9/2/2016                                  YES

H                                    SUPPLIER-B                     11/15/2016                              NO

I                                      SUPPLIER-C                    5/26/2016                                NO

J                                      SUPPLIER-C                    5/5/2017                                 NO


Many thanks for your help in advance,

Eric

1 Solution

Accepted Solutions
sth_wch004
New Contributor III

Re: QlikSense Check for 30 days date of records from sub-group

Hi Eric,

I am not sure if this is the correct way

Capture.PNG

TEMP1:

LOAD * INLINE

[

DEPARTMENT, VENDOR,  DATE

'A','SUPPLIER-A','4/5/2016'

'B','SUPPLIER-A','3/22/2016'

'C','SUPPLIER-A','10/18/2016'

'D','SUPPLIER-B','6/20/2016'

'E','SUPPLIER-B','7/10/2016'

'F','SUPPLIER-B','8/5/2016'

'G','SUPPLIER-B','9/2/2016'

'H','SUPPLIER-B','11/15/2016'

'I','SUPPLIER-C','5/26/2016'

'J','SUPPLIER-C','5/5/2017'

]

;

TEMP2:

LOAD

*,

IF([VENDOR] = PREVIOUS([VENDOR]) , [DATE] - PREVIOUS([DATE])) AS 'INTERVAL_1'

RESIDENT [TEMP1]

ORDER BY [VENDOR],[DATE] ASC

;

DROP TABLE [TEMP1];

TEMP3:

LOAD

*,

IF([VENDOR] = PREVIOUS([VENDOR]) AND ISNULL(INTERVAL_1), FABS([DATE] - PREVIOUS([DATE])), [INTERVAL_1]) AS 'INTERVAL'

RESIDENT [TEMP2]

ORDER BY [VENDOR],[DATE] DESC

;

DROP TABLE [TEMP2];

DROP FIELD [INTERVAL_1];

TABLE:

LOAD

*,

IF(INTERVAL<=30, 'YES','NO') AS '30 DAYS CHECK?'

RESIDENT [TEMP3]

;

DROP TABLE [TEMP3];

4 Replies
sth_wch004
New Contributor III

Re: QlikSense Check for 30 days date of records from sub-group

Hi Eric,

Like this?

TEMP:

LOAD * INLINE

[

DEPARTMENT, VENDOR,  DATE

'A','SUPPLIER-A','4/5/2016'

'B','SUPPLIER-A','3/22/2016'

'C','SUPPLIER-A','10/18/2016'

'D','SUPPLIER-B','6/20/2016'

'E','SUPPLIER-B','7/10/2016'

'F','SUPPLIER-B','8/5/2016'

'G','SUPPLIER-B','9/2/2016'

'H','SUPPLIER-B','11/15/2016'

'I','SUPPLIER-C','5/26/2016'

'J','SUPPLIER-C','5/5/2017'

]

;

TABLE:

LOAD

*,

IF([VENDOR] = PREVIOUS([VENDOR]),  [DATE] - PREVIOUS([DATE])) AS 'INTERVAL',

IF([VENDOR] = PREVIOUS([VENDOR]) AND [DATE] - PREVIOUS([DATE])<= 30,'YES','NO') AS '30 DAYS CHECK?'

RESIDENT [TEMP]

ORDER BY [VENDOR],[DATE] ASC

;

DROP TABLE [TEMP];

osnap822
New Contributor II

Re: QlikSense Check for 30 days date of records from sub-group

Dear Chris,

Thanks for the reply. Just one more thing please, can we also compare the first record against 2nd item? Reason is I need to highlight all records within 30 days?

Say for the above sample table both ''A','SUPPLIER-A','4/5/2016'' and 'B','SUPPLIER-A','3/22/2016' should also be marked as YES when compared to each other?

Thanks Again!

Eric

sth_wch004
New Contributor III

Re: QlikSense Check for 30 days date of records from sub-group

Hi Eric,

I am not sure if this is the correct way

Capture.PNG

TEMP1:

LOAD * INLINE

[

DEPARTMENT, VENDOR,  DATE

'A','SUPPLIER-A','4/5/2016'

'B','SUPPLIER-A','3/22/2016'

'C','SUPPLIER-A','10/18/2016'

'D','SUPPLIER-B','6/20/2016'

'E','SUPPLIER-B','7/10/2016'

'F','SUPPLIER-B','8/5/2016'

'G','SUPPLIER-B','9/2/2016'

'H','SUPPLIER-B','11/15/2016'

'I','SUPPLIER-C','5/26/2016'

'J','SUPPLIER-C','5/5/2017'

]

;

TEMP2:

LOAD

*,

IF([VENDOR] = PREVIOUS([VENDOR]) , [DATE] - PREVIOUS([DATE])) AS 'INTERVAL_1'

RESIDENT [TEMP1]

ORDER BY [VENDOR],[DATE] ASC

;

DROP TABLE [TEMP1];

TEMP3:

LOAD

*,

IF([VENDOR] = PREVIOUS([VENDOR]) AND ISNULL(INTERVAL_1), FABS([DATE] - PREVIOUS([DATE])), [INTERVAL_1]) AS 'INTERVAL'

RESIDENT [TEMP2]

ORDER BY [VENDOR],[DATE] DESC

;

DROP TABLE [TEMP2];

DROP FIELD [INTERVAL_1];

TABLE:

LOAD

*,

IF(INTERVAL<=30, 'YES','NO') AS '30 DAYS CHECK?'

RESIDENT [TEMP3]

;

DROP TABLE [TEMP3];

osnap822
New Contributor II

Re: QlikSense Check for 30 days date of records from sub-group

Dear Chris,

Thanks again! Yes this is the result I was expecting.

I found out there's no reverse function for Previous(), did some testing and similar to your solution i created a new dimension (RecNo()+ 1 ) as [ORDER]. But i wouldn't discover it without your help! Thank you!