topic Re: Date filter doesn't filter correctly in QlikView

Date filter doesn't filter correctly

Wed, 25 Feb 2015 17:38:30 GMT

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!

Re: Date filter doesn't filter correctly

yura_ratu — Wed, 25 Feb 2015 17:50:35 GMT

Try this in load script

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

Re: Date filter doesn't filter correctly

Wed, 25 Feb 2015 17:59:36 GMT

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

Re: Date filter doesn't filter correctly

yura_ratu — Wed, 25 Feb 2015 18:02:30 GMT

Could you attach your qvw?

Re: Date filter doesn't filter correctly

Anonymous — Wed, 25 Feb 2015 18:03:32 GMT

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

Re: Date filter doesn't filter correctly

Wed, 25 Feb 2015 18:07:01 GMT

In the Chart Dimension i tried:

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

Doesn't work either.

Re: Date filter doesn't filter correctly

Wed, 25 Feb 2015 18:09:02 GMT

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

Re: Date filter doesn't filter correctly

Anonymous — Wed, 25 Feb 2015 18:11:57 GMT

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

Re: Date filter doesn't filter correctly

Anonymous — Wed, 25 Feb 2015 18:13:38 GMT

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

Re: Date filter doesn't filter correctly

Anonymous — Wed, 25 Feb 2015 18:19:00 GMT

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.

Re: Date filter doesn't filter correctly

Wed, 25 Feb 2015 18:36:20 GMT

Here is my qvw file…thanks for looking into it.

The sub report that having problem is Actions Completed.

Enter date range 11/1/2014-11/9/2014 …only show data on 11/7/2014 ….i have data on 11/8/2014

If enter 11/1/7/2014 – 11/9/2014 …it will pull data on 11/8/2014 as well

Thanks….

Re: Date filter doesn't filter correctly

Colin-Albert — Wed, 25 Feb 2015 18:40:17 GMT

Your dates most probably include a time component as well.

In your load script, if you use date(your_date_field) as datefield the text portion of the dual will be formatted as the date, but the underlying number will still have the decimal (time) component so the listbox will only select values up to midnight - effectively excluding the values during the selected date..

If you use date(floor(your_date_field)) as date_field then the data is rounded down to an integer and the time component is always 00:00 (midnight), and your selection will work.

The answer is to use Date(floor(xxx)) in your load script for the date fields.

If you need the time vale as well then load a separate field using time(frac(xxx))

Re: Date filter doesn't filter correctly

Wed, 25 Feb 2015 19:39:40 GMT

I try to use this in my Load statement:

Load

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

But now Enter date range 11/4/2014-11/6/2014, it show data 11/5/2014-11/6/2014.....it doesn't show data 11/4/2014(which is the From Date)....it load data Through but not From.

In the Document properties > Triggers > i have variable event Trigger set as

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

Is there anything i need to change to make this work?

Re: Date filter doesn't filter correctly

Colin-Albert — Wed, 25 Feb 2015 20:06:53 GMT

Using FLOOR in your dates will remove the time component.

Have you tried removing the time from your trigger.

Check the date_field in a list box to see how they are formatted now. There should be no time component.

Re: Date filter doesn't filter correctly

Wed, 25 Feb 2015 20:14:40 GMT

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],""))

Re: Date filter doesn't filter correctly

Colin-Albert — Wed, 25 Feb 2015 22:07:13 GMT

Good to hear your issue is fixed.

Can you mark the posts that are correct / helpful as this will assist others with similar problems.