topic Compare dates in set analysis in QlikView

Compare dates in set analysis

Thu, 14 Apr 2011 19:05:54 GMT

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.

Compare dates in set analysis

erichshiino — Thu, 14 Apr 2011 19:30:24 GMT

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

Compare dates in set analysis

johnw — Fri, 15 Apr 2011 00:51:19 GMT

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)