topic How to compare between date field and a date value? in QlikView

How to compare between date field and a date value?

Anonymous — Sun, 27 Nov 2016 13:23:07 GMT

hi,

can I please have your assistance with defining properly a count if statement, i would like to count all the id's when their time_stamp is equal to yesterday date.

my initial try (which didnt succeeded of course):

count(distinct if(date(time_stamp)=Today-1,id))

*time_stamp - date+hour field in my data set

*id - unique key value in my data set

thanks,

Tal

Re: How to compare between date field and a date value?

vinieme12 — Sun, 27 Nov 2016 13:40:37 GMT

when you do today()-1 you will be returned a number not a date

so try

count(distinct if(FLOOR(timestamp#(time_stamp,'MM/DD/YYYY hh:mm:SS'))=Today()-1,id))

i've assumed your time_Stamp format to be = MM/DD/YYYY hh:mm:SS << replace as per your format

Re: How to compare between date field and a date value?

MarcoWedel — Sun, 27 Nov 2016 20:43:24 GMT

try with

DayName(time_stamp)

DayStart(time_stamp)

or just

Floor(time_stamp)

instead of

date(time_stamp)

as date() does not change the numerical part of a dual value.

hope this helps

regards

Marco