Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
osnap822
Contributor II
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
Contributor III
Contributor III

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

View solution in original post

4 Replies
sth_wch004
Contributor III
Contributor III

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
Contributor II
Contributor II

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
Contributor III
Contributor III

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

View solution in original post

osnap822
Contributor II
Contributor II

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!