Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amars
Specialist
Specialist

Null date comparison issue in SET Analysis

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

4 Replies
sunny_talwar

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

jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
amars
Specialist
Specialist
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein