Date filter doesn't filter correctly

[#1]

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!

[#2]

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….

[Colin-Albert]

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))

[#4]

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?

[Colin-Albert]

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.

[#6 · Accepted solution]

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

[Colin-Albert]

Good to hear your issue is fixed. 

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