topic Date greater than Variable - Set Analysis in QlikView

Date greater than Variable - Set Analysis

Mon, 11 Apr 2011 11:08:48 GMT

Hello All,

In my script I set up a variable:

LET v90 = num(today()-90);

Within the charts etc, I need to count the number of cases where the date is greater or equal to the variable;

Something like Count({$<Date = {'>=$(v90)}>}Case)

Just cant work out the correct syntax! Any Help would be great thanks!

BR.

SV:Date greater than Variable - Set Analysis

gandalfgray — Mon, 11 Apr 2011 11:24:33 GMT

Try this:

In the load script:

Let v90 = Date(today()-90);

In the charts expression:

count({$<Date={">=$(v90)"}>} Case)

SV:Date greater than Variable - Set Analysis

Mon, 11 Apr 2011 12:19:17 GMT

Let v90 = Date(today()-90);

is generating a date of 30/12/1899

Any ideas?

Date greater than Variable - Set Analysis

rahulgupta — Mon, 11 Apr 2011 12:38:22 GMT

Hi,

You have use num() in your Variable declaration syntax and comparing a date field with that..May be this may be the Reason for data Type Mismatch...

So,you may convert the Variable v90 to the Date Data Type.

SV:Re: SV:Date greater than Variable - Set Analysis

gandalfgray — Mon, 11 Apr 2011 13:03:29 GMT

I don't know really.

Let v90 = Date(today()-90);

is generating a date of 01/11/2011

for me (as expected).

SV:Date greater than Variable - Set Analysis

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 14:10:23 GMT

Hello,

What it may be happening here is that your date is being evaluated twice, so Date(Today()-90) = "11/01/2011" so its doing 11 / 1 / 2011 which returns a decimal numeric value of 0,000546. Date(0,000546) is actualy 30/12/1899 which is the epoch (day zero) for QlikView. Further explanation on this behavior an be found here.

How are you building your expressions? How are you expanding the variable?

Hope that helps.

Re: Date greater than Variable - Set Analysis

rcurlewis — Tue, 07 Jun 2016 18:06:18 GMT

Hi Jeremy,

Not sure this will resolve you issue, but I found that when I create a variable on the front end (not via the script), I HAVE to include the "=" sign before the expression. When you check a TEXT box, both works, but when you put it in an expression the one without the "=" does not work.

I.e. My Variable vMaxPeriod defined as: =Max([Fin Period])

and my Expression: Avg({<[Fin Period]= {'$(vMaxPeriod)'}>} [Summary Score])

Hope this helps

Re: Date greater than Variable - Set Analysis

Sat, 02 Jul 2016 07:04:03 GMT

Thanks Robert, that solved a problem that had been driving me nuts for hours!

Re: Date greater than Variable - Set Analysis

rcurlewis — Thu, 21 Jul 2016 14:27:06 GMT

Hi Jeremy,

Maybe the easiest would be this:

=count(if(Date >= v90, 1))

=count(if(Date >= $(v90), 1))

But I understand that this is not in the Set Analysis....

Regards