topic Re: filter not working... in Qlik NPrinting

filter not working...

markp201 — Thu, 09 Mar 2017 15:45:43 GMT

We have a datetime field we need to filter for prior day with a static expression

closedate>today()-1 AND closedate<today()

For 3/9 we need closedate between

3/7/17 12:00 am thru 3/8/17 12:00 am

When we run the task, the filter appears to be ignored and end up getting everything.

One technique we're trying is changing the PC time for testing but looks like qlikview ignores the change.

aarkay29 — Thu, 09 Mar 2017 16:05:35 GMT

closedate>Date(today()-1) AND closedate<Date(today())

Also check the format of the closedate in the database and try formatting the today accordingly.

Today()-1 will return a number instead date format that might be the reason for ignoring the where condition!!!

njmaehler — Thu, 09 Mar 2017 16:15:18 GMT

Hi Mark,

I would exclude the time with the Date function but in reality you actually should not be getting anything because you are saying

closedate>today()-1 AND closedate<today()

But if you are on 3/9 then Today() -1 = 8/3 therefore you are saying

closedate>8/3 AND closedate<9/3 which is actually nothing since you need to say

closedate >= 8/3 (greater than and equal to 8/3) and closedate < 9/3 (just less than 9/3)

It is the greater than and equal to that will make the difference plus wrap it in date i.e.

closedate >= Date(today()-1) AND closedate < Date(today())

As far as this comment goes:

For 3/9 we need closedate between

3/7/17 12:00 am thru 3/8/17 12:00 am

I am reading it as you need everything for 7/3 since it starts as 12:00 am and runs until 12:59:59 pm

i.e. less than 8/3 12:00 am

If you are on the 9/3 then if you wanted 7/3 then it is Date(Today() -2)

Hope that helps,

Nicky

markp201 — Thu, 09 Mar 2017 17:38:47 GMT

This was the solution

Values: =floor(closedate)=(today()-1)

Number: -1

IsNumeric: yes

Evaluate: no

Verify filter: yes

Unit test case

=floordate(closedate)=(Date('2/16/17')-1)