Solved: Date filter doesn't filter correctly - Qlik Community

Report Inappropriate Content · ‎2015-02-25

I am having problem where when i enter the date from 11/01/2014 through 11/09/2014 in my input box, it only pulls data from 11/01/2014-11/08/2014....it doesn't pull data that dated 11/09/2014.

What am i missing?

In the Load statement, i have it set as:

LOAD

Date("CollectorAudit.DateActionCompleted",'MM/DD/YYYY h:mm:ss[.fff] TT') as "Action Completed",

In my Chart properties for the date dimension i have it set as

=Date(if([Action Completed]>ActionCompletedFrom and [Action Completed]<ActionCompletedTo,[Action Completed],""))

in the Document Properties > Triggers > variable Event Trigger for date i have it set to

='>=' & ActionCompletedFrom &' 12:00:00 AM' & '<=' & ActionCompletedTo &' 11:59:59 PM'

Any help will be greatly appreciated....Thanks!

Report Inappropriate Content · ‎2015-02-25

Thank you, it is working now. Thank you all who provided feedbacks.

I am using the following for load script:

Load

Date(Floor("CollectorAudit.DateActionCompleted")) as "Action Completed",

Then in the Chart properties > Dimension Expression

=Date(if([Action Completed]>=ActionCompletedFrom and [Action Completed]<=ActionCompletedTo,[Action Completed],""))

View solution in original post

yura_ratu · ‎2015-02-25

Try this in load script

Date("CollectorAudit.DateActionCompleted",'MM/DD/YYYY h:mm:ss TT') as "Action Completed",

Report Inappropriate Content · ‎2015-02-25

Try this in Load script...doesn't work either.

In the Document Properties > Number setting for Action Completed , i select Number format = Date ; Format pattern : MM/DD/YYYY h:mm:ss TT

yura_ratu · ‎2015-02-25

Could you attach your qvw?

Anonymous · ‎2015-02-25

did you try <= instead of < in your chart dimension?

Report Inappropriate Content · ‎2015-02-25

In the Chart Dimension i tried:

=Date(if([Action Completed]>ActionCompletedFrom and [Action Completed]<=ActionCompletedTo,[Action Completed],""))

Doesn't work either.

Report Inappropriate Content · ‎2015-02-25

Due to customer's data integrity issue, i am not allow to upload qvw file.

Anonymous · ‎2015-02-25

can you post a sample qvw? formatting with dates can be a little tricky sometimes but we can take a look.

Anonymous · ‎2015-02-25

you could scramble and reduce data so no meaningful data gets transmitted and customer's data integrity is still in tact. check this document out http://community.qlik.com/docs/DOC-1290

Anonymous · ‎2015-02-25

I'd rather avoid using hh:mm:ss altogether. That is, it is safer to use <'11/10/2014' than <='11/09/2014 11:59:59 PM' - fewer possibilities to make mistakes in format.