Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Amar do you have a sample we can take a look at?
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)
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
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.