Small or Egual function not working

[aydiniletisim]

Why first script load RecordDates "19/09/2022,20/09/2022,21/09/2022,22/09/2022,23/09/2022,24/09/2022" and second script load RecordDates "19/09/2022,20/09/2022,21/09/2022,22/09/2022,23/09/2022,24/09/2022,25/09/2022" ? Are they not the same statement?

[MendyS]

Hi @aydiniletisim 

I am not sure about that - but I think its the way QLIK saves dates in the memory,

can you wrap the dates with the Date function and tell me if it still happens?  

[RafaelBarrios]

hi @aydiniletisim 

I cant be sure why it is doing that, but I would suggest using makedate to make sure you compare dates with dates and not dates with text.

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeF...

so, your where statement should be something like:

where date(RecordDate,'DD/MM/YYYY') >=makedate(2019,09,19) and date(RecordDate,'DD/MM/YYYY') <=makedate(2019,09,25)

Best,

[aydiniletisim]

Sorry my English is not quite sharp, I am not perfectly sure understand you quite well. If your suggestion this script, it worked.

where Date(RecordDate,'DD/MM/YYYY')>=Date(44823,'DD/MM/YYYY') and Date(RecordDate,'DD/MM/YYYY')<=Date(44829.9999884259,'DD/MM/YYYY')

[aydiniletisim]

Your statement is not worked, it still misses 25/09/2022. I guess my statement your statement tells qlik sense bring small or equal values to RecordDate 25/09/2022 00:00:00.  Qlik sense not bring any data from 25/09/2022 because all the data in 25/09/2022 happened later hours than 00:00:00.

[MendyS]

@aydiniletisim 

try this - "where date(date#(RecordDate,'DD/MM/YYYY'),'DD/MM/YYYY') >=date(date#('19/09/2022','DD/MM/YYYY'),'DD/MM/YYYY')

and date(date#(RecordDate,'DD/MM/YYYY'),'DD/MM/YYYY') >=date(date#(25/09/2022,'DD/MM/YYYY'),'DD/MM/YYYY')

you can read more about that here - Solved: The difference between Date() and Date#() - Qlik Community - 12426

[Or · Accepted solution]

Seems like you've answered your own question here...

e.g. 25/09/2022 06:00:00 is not smaller than or equal to 25/09/2022. They would translate into 44829.25 and 44829.0 respectively. When dealing with timestamps, a date without a timestamp is assumed to mean midnight.

[aydiniletisim]

this one does not work either because "date(date#('25/09/2022','DD/MM/YYYY'),'DD/MM/YYYY')" and "date(date#(RecordDate,'DD/MM/YYYY'),'DD/MM/YYYY')" produce null values.