topic Comparing date field with variable in QlikView

Comparing date field with variable

Fri, 20 Nov 2020 18:02:30 GMT

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.

Comparing date field with variable

Mon, 19 Mar 2012 09:30:43 GMT

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?

Comparing date field with variable

CELAMBARASAN — Mon, 19 Mar 2012 09:42:12 GMT

Hi,

How you assigned the variable value?

Celambarasan

Comparing date field with variable

clisboa_noesis — Mon, 19 Mar 2012 09:47:13 GMT

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

Comparing date field with variable

Mon, 19 Mar 2012 11:45:43 GMT

Thank you Celambarasan and Carlos.

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