3 Replies Latest reply: Apr 13, 2018 1:34 AM by Daniel Trautmann RSS

    Analytics on time difference / issue on filtering

    Daniel Trautmann

      Hi all,

      I have a specific problem when creating dashboards with a customised (grouped) dimension using the difference of two dates (in days).

       

      I have two tables:

      Table A (ID, Date1) and Table B (ID, Date2)

       

      I created a customised dimension using something like:

      =if(interval(Date1-Date2,'d')>=0 and interval(Date1-Date2,'d')<=1,'0-1 days',

      if(interval(Date1-Date2,'d')>=2 and interval(Date1-Date2,'d')<=5,'2-5 days',

      if(interval(Date1-Date2,'d')>=6 and interval(Date1-Date2,'d')<=14,'6-14 days',

      if(interval(Date1-Date2,'d')>=15 and interval(Date1-Date2,'d')<=30,'15-30 days',

      if(interval(Date1-Date2,'d')>=31 and interval(Date1-Date2,'d')<=1,'31-90 days',

      '>90 days')))))

       

      This works as aspected showing something like:

       

      However, if I click on one of the bars / groups to filter, the filter does not work as aspected as it uses the underlying dates (Date1 and Date2) instead of doing a filter on the customised dimension. For example if click on '0-1 days'. I receive the following result:

       

      Date2 = 20/01/2017 and Date1 = 21/01/2017 (correct)

      Date2 = 22/01/2017 and Date1 = 22/01/2017 (correct)

      Date2 = 20/01/2017 and Date1 = 22/01/2017 (incorrect)

       

      The reason for the incorrect line is that Qlik Sense does filter on Date2=20/01/2017 (because of the 1st line) and Date1=22/01/2017 (because of the 2nd line) and therefore also displayes the incorrect 3rd line.

       

      One of my ideas to resolve this was (instead of creating the custom dimension) to create this group directly in the data load editor by using joins/lookup functions but I constantly fail to do this

       

      Hopefully my problem is clear and somebody can help. Unfortunately, I will not be able to share the actual project.

       

      Many thanks in advance!!