Comparing date field with variable

[#1]

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.

[#2]

I fixed it by calculating the variable one more time after the first dollar sign:

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

Why didn't it calculate the variable after the first dollar sign? Any ideas?

[CELAMBARASAN]

Hi,

     How you assigned the variable value?

Celambarasan

[clisboa_noesis · Accepted solution]

Hi tigekala,

The problem is quite simple, when you define the expression of a variable if you place an '=' in the beginning, the result is evaluated always. If you don't place the '=' the expression is only evaluated when you do a dollar expansion like you did when you repetead the dollar sign.

It's usefull to not put the equal sign when you want to define an expression that should only be evaluated in a graph for each dimension.

Hope this shed some light into your issue (and future ones )

Regards,

Carlos

[#5]

Thank you Celambarasan and Carlos.

I defined the variables without the equasion mark. d'oh!