Why my date comparison in set analysis does not work?

[msmichael]

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

[nithin149 · Accepted solution]

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

[maxgro · Accepted solution]

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

[msmichael]

Thank you so much Nithin. It works well. 

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

[msmichael]

thank you Maxgro too!