Analytics on time difference / issue on filtering

[Anonymous]

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!!

[Anil_Babu_Samineni]

Perhaps use Dual()

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

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

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

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

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

Dual('>90 days',5))))))

[Anonymous]

Hi,

thanks for your reply. Unfortunately, it doesn't work. The result is still the same.

[Anonymous]

Anyone?