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

[Anonymous]

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

[sth_wch004]

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

[Anonymous]

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 · Accepted solution]

Hi Eric,

I am not sure if this is the correct way

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

[Anonymous]

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!