Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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!!!
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
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)