Using a variable in Set Analysis and script

[#1]

Hello

I'm having a problem using the same variable in the script and Set Analysis.

For example I want to load all the years, months and days from a predefined date QVD until the end of the current year. To do so I defined the following variable via Variable Overview (without outer quotes):

vMaxYear: "=text(Date(today(), 'YYYY'))"

To define it that way has the benefit of seeing the calculated value in an input box and especially it works in a Set Analysis like in a title string (without outer quotes):

"=MinString({1<YEAR={"=$(vMaxYear)"}>} DAY&'.'&MONTH&'.'&YEAR)"

Yet it does not work in the script:

Date:

LOAD 

    YEAR, MONTH, DAY

FROM [..\shared\qvd\DATE.QVD](qvd)

WHERE YEAR<=$(vMaxYear);

Until now I tried many combinations of double/single quotes, equal signs and using/leaving dollar expansion but none of them worked in both application fields with only one definition of the variable. Yet I came to the result that leaving the = in the definition the script runs well but the Set Analysis does not.

For now I prefer the definition with = since I can see the value in an input box but it just won't work in the script. Is there a solution for both using only a single variable?

Thanks in advance

[Gysbert_Wassenaar]

Try creating vMaxYear as Year(Today()). You're now comparing numeric YEAR values with a text value.

[rubenmarin]

Hi, for me works with this:

variable: LET vMaxYear = Year(Today());

script: WHERE YEAR<=$(vMaxYear)

expression: Sum({<Year={$(vMaxYear)}>} Amount)

[swuehl · Accepted solution]

Yes, define your variable in the script using LET, i.e.

Let vTest2 = Text(Date(Today(),'YYYY'));

or as Gybert suggested using Year() function.

If you define it in the variable overview, the Dollar sign expansion in the script will contain the equal sign, leading to

WHERE YEAR <= =Text(Date(Today(),'YYYY'))

-->Syntax invalid

[#5]

Alright, that does the trick

Using let the values are created during script execution time but that shouldn't be a problem in my current case. Sadly there's only one answer to mark as the right one. Ruben got me on the right track at first but yours is more precisely.

I also saw the invalid syntax but hoped for another mechanism which unlike dollar expansion would evaluate the function at first.

Gysbert mentioned the Year() function and this one works well but sometimes it is important how the data are formatted and date() gives a better handling imho - combined with text() or num() it should even return the right data type.

[swuehl]

Dollar sign expansion will anyway expand the variable to plain constant number in the script code before parsing, so no need to worry about right data types.

The Magic of Dollar Expansions

Data Types in QlikView

I would go for just using (as suggested above)

LET vMaxYear = Year(Today() );

It's just much easier to understand.

[rubenmarin]

Hi, If you do LET vMaxYear = Date(Today(), 'YYYY'); --> QV stores the string 2015 and the value 22/10/2015

If you create a text box with expression "=AddMonths(vAño, 3)" --> returns 22/01/2016

So be aware of what value you're really saving and if you go with the date version consider to use EndYear(), EndMonth(), Today()... as it will change the value you're storing (it will show all as '2015').

If you use LET vMaxYear = Text(Date(Today(), 'YYYY')); --> Then you're storing a text, not a date, and date functions like AddMonths() will fail.

[#8]

Well I don't want to fight about it but in my case vMaxYear might be used in comparing combined fields of year and month so it's neccessary to have a 4 char year and a 2 char month - using text(date(today(),'MM')) it is save to have leading 0s for string operations whilst set analysis does not care.

Just my two cents - maybe I'll see the perks of year(), month(),.. in the future