3 Replies Latest reply: Mar 9, 2017 12:38 PM by MARK PERRONE RSS

    filter not working...

    MARK PERRONE

      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.

        • Re: filter not working...
          Aar Kay

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

          • Re: filter not working...
            Nicky Maehler

            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

            • Re: filter not working...
              MARK PERRONE

              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)