topic Re: Null date comparison issue in SET Analysis in QlikView

Null date comparison issue in SET Analysis

amars — Wed, 11 May 2016 02:20:13 GMT

Hi all,

I am using the below expression to compare a set of dates between a date range. If any of the date lies within the selected date range then I need to count the record. Now if one of the dates is null all Employee_ID's are counted. How to change the code to avoid that?

=Count(Distinct

<[First_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_ST_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'}> +

<[First_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_END_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'}> +

<[Second_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_ST_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'}> +

<[Second_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_END_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'}> } Employee_ID)

Thanks in advance...

With Best Regards

Amar

Re: Null date comparison issue in SET Analysis

sunny_talwar — Wed, 11 May 2016 02:25:55 GMT

Amar do you have a sample we can take a look at?

Re: Null date comparison issue in SET Analysis

jonathandienst — Wed, 11 May 2016 05:24:53 GMT

Its because your expression is a union of all four terms. So the null value will cause two of the terms to return nothing, but the other terms still work. Add all four fields to each part of the union. Something like:

=Count(Distinct

<[First_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_ST_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[Second_Date] = {'*'},

[APPOINTMENT_END_DATE] = {'*'}> +

<[First_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_ST_DATE] = {'*'},

[Second_Date] = {'*'},

[APPOINTMENT_END_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'}> +

<[First_Date] = {'*'},

[APPOINTMENT_ST_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[Second_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_END_DATE] = {'*'}> +

<[First_Date] = {'*'},

[APPOINTMENT_ST_DATE] = {'*'},

[Second_Date] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'},

[APPOINTMENT_END_DATE] = {'>=$(=Date(vDtRangeReportFrom))<=$(=Date(vDtRangeReportTo))'}> }

Employee_ID)

Re: Null date comparison issue in SET Analysis

amars — Wed, 11 May 2016 06:21:11 GMT

Thanks Jonathan/Sunny,

I understand its a union, my issue is Qlikview returns records where both First_Date & Second_Date is null.

However I am not able to replicate the issue in a Test App.

I have converted the chart expression to a normal expression & it is working fine. Not sure for the behavior in my actual application.

Best Regards

Amar

Re: Null date comparison issue in SET Analysis

jonathandienst — Wed, 11 May 2016 08:15:57 GMT

Well then something is subtly different between your actual application and the test document. Perhaps the formats of the date fields? You might want to make these explicit in the load statement.