Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dtrautmann
New Contributor

Analytics on time difference / issue on filtering

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

3 Replies

Re: Analytics on time difference / issue on filtering

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

dtrautmann
New Contributor

Re: Analytics on time difference / issue on filtering

Hi,

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

dtrautmann
New Contributor

Re: Analytics on time difference / issue on filtering

Anyone?

Community Browser