Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

1 Solution

Accepted Solutions
clisboa_noesis
Contributor

Comparing date field with variable

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

4 Replies
Not applicable

Comparing date field with variable

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

Hi,

     How you assigned the variable value?

Celambarasan

clisboa_noesis
Contributor

Comparing date field with variable

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

Not applicable

Comparing date field with variable

Thank you Celambarasan and Carlos.

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