4 Replies Latest reply: Nov 24, 2017 3:51 AM by eric nieto RSS

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

    eric nieto

      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

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

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

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

              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

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

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