topic Re: Why my date comparison in set analysis does not work? in QlikView

Why my date comparison in set analysis does not work?

msmichael — Sat, 16 Nov 2024 05:13:58 GMT

Hi Expert,

I have a table with two fields - Report ID and Latest Access Date.

Each Report ID can have multiple "Latest Access Date". I want to count how many "Latest Access Date" for each Report ID given a period (vStartDate and vEndDate are two variables linked to two input calendars).

I added a straight table, used "Report ID" as dimension, and added an expression like below:

=count({<[Latest Access Date]={">$(=vStartDate)<$(=vEndDate)"}>} [Latest Access Date])

However, the formula only gave me 0.

Where am I wrong? My qvw file is attached.

Thanks

Re: Why my date comparison in set analysis does not work?

nithin149 — Thu, 15 Nov 2018 22:09:32 GMT

Use this

you date format also should match the column formating

=count({<[Latest Access Date]={">$(=Date(vStartDate,'MM/DD/YYYY h:mm:ss TT'))<$(=date(vEndDate,'MM/DD/YYYY h:mm:ss TT'))"}>} [Latest Access Date])

Re: Why my date comparison in set analysis does not work?

maxgro — Thu, 15 Nov 2018 22:10:00 GMT

=count({$<[Latest Access Date]={">$(=Date(vStartDate, 'MM/DD/YYYY hh:mm:ss TT'))<$(=Date(vEndDate, 'MM/DD/YYYY hh:mm:ss TT'))"}>} [Latest Access Date])

Re: Why my date comparison in set analysis does not work?

msmichael — Fri, 16 Nov 2018 01:58:12 GMT

Thank you so much Nithin. It works well.

I thought QlikView should be able to do the comparison as both are numeric values...

Re: Why my date comparison in set analysis does not work?

msmichael — Fri, 16 Nov 2018 01:59:42 GMT

thank you Maxgro too!