4 Replies Latest reply: Mar 19, 2012 7:45 AM by Meelis Karulin RSS

    Comparing date field with variable

      Hello. I'm new to qlikview and have been trying to solve this rather basic date formula without luck:

       

      In my load script i have this:

       

      Main:

      LOAD

      [Dok. nr.] AS SPOrder,

        num(Date#( [Dok. kuupäev], 'DD.MM.YYYY')) as tDate,

      ....

       

      I have 2 saved variable called vSPCampaignStart:

      num(Date#('16.11.2011','DD.MM.YYYY'))

       

      ...and vSPCampaignEnd:

      num(Date#('06.11.2012','DD.MM.YYYY'))

       

      tDate outputs in format :

      40180

      40181

      etc....

       

      variables output:

      40863

      40914

       

      The queston is: How do i compare the tDate with those two variables?

      My current formula is:

      =Count({<tDate = {">=$(vSPCampaignStart)<=$(vSPCampaignEnd)"}>}SPOrder)

      which outputs 0

       

      When i replace variables in the formula with date numbers like this:

      =Count({<tDate = {">=40863<=40914"}>}SPOrder)

      i get 276, which is expected.

       

      Any help would be appreciated, ive spent large portion of my weekend trying to solve this

       

      PS: i'm using QlikView personal edition and can't open files.