Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Compare dates in set analysis

Hi All,

Thanks for all your help.

i have a date field and want to compare it against the date 15 days from today.i tried something like this.

=count({<[Date Last Cert Create]={">=$(=Today()-15)"}>} distinct Vendor_ID)

and it gives no results.

and my date field has only days ie 1,2,3,...

Thanks.

2 Replies
erichshiino
Partner - Master
Partner - Master

Hi,

I have some problems with dates in set analysis as well but it always works when I use numbers instead of dates.

[Date Last Cert Create] is a date, right? (like 31/12/2010). It`s easier if you create a field on the data model as a number.

For example

Load ...

num([Date Last Cert Create]) as nDateLastCertCreate

After that, I would do the same with the today()-15

The result would look like this

=count({<[nDateLastCertCreate]={">=$(=num(Today()-15))"}>} distinct Vendor_ID)

Hope it helps,

Erich

johnw
Champion III
Champion III

Or go the other direction and convert the number to a date. Either way, the point is that set analysis isn't smart enough to recognize that dates and numbers can be treated as the same sort of thing. Technically, it's doing a text comparison of the formatted values, I believe. So just format it properly. Assuming your default date format is correct, the below. If you have a different date format, specify it explicitly in the date() function.

count({<[Date Last Cert Create]={">=$(=date(Today()-15))"}>} distinct Vendor_ID)