topic Re: Variable in set expression not evaluating in QlikView

Variable in set expression not evaluating

Tue, 02 Jun 2015 17:13:29 GMT

I have created a variable called vLastWeek, which is the last week of the data set, regardless of the selections:

set vLastWeek = "max({1<WeekNumber>}WeekNumber)";

If I show the value of this variable in a text object, I get the correct value.(='This value shows ok: ' & $(vLastWeek))

This evaluates to 5 correctly.

However, if I use it in another expression, it does not evaluate correctly. Here is an example:

This will display 0 in the Text object: =Sum({$<WeekNumber = {'$(vLastWeek)'}>} Sales)

and this will display the correct sum of sales for the last week (25😞 =Sum({$<WeekNumber = {'5'}>} Sales)

What am I doing wrong?

Thanks guys!

Here my sample data:

Load * Inline

[

WeekNumber,Sales,Purchases,Category

1,10,20,'C'

1,3,10,'S'

2,30,5,'C'

2,20,15,'S'

3,35,40,'C'

3,25,34,'S'

4,20,25,'C'

4,15,16,'S'

5,15,3,'C'

5,10,7,'S'

] ;

Text-to-speech function is limited to 100 characters

Options : History : Help : Feedback

Re: Variable in set expression not evaluating

ramoncova06 — Tue, 02 Jun 2015 18:12:18 GMT

have you tried without the single quotes

=Sum({$<WeekNumber = {$(vLastWeek)}>} Sales)

Re: Variable in set expression not evaluating

maxgro — Tue, 02 Jun 2015 18:20:02 GMT

try with

Sum({$<WeekNumber = {'$(=$(vLastWeek))'}>} Sales)

Re: Variable in set expression not evaluating

caio_caminoski — Tue, 02 Jun 2015 18:23:08 GMT

Have you tried using let instead of set?

LET vLastWeek = max({1<WeekNumber>}WeekNumber);

If you use SET, you should do what Massimo Grossi said in order to evaluate your variable

Regards

Caio

Re: Variable in set expression not evaluating

ramoncova06 — Tue, 02 Jun 2015 18:24:09 GMT

just realized what was going on, what Massimo mentions should work in order to extend the variable

=Sum({$<WeekNumber = {"$(=$(vLastWeek))"}>} Sales)

Re: Variable in set expression not evaluating

Wed, 03 Jun 2015 10:14:19 GMT

This works, thanks! But I have no idea why '$(vLastWeek)' does not work and '$(=$(vLastWeek))' does. If you have a few seconds would you help understand, Massimo?

Thank you so much!

Re: Variable in set expression not evaluating

maxgro — Wed, 03 Jun 2015 11:38:58 GMT

inner $ is for variable, outer $ is for dollar expansion (dse)

if you remove the lable you can see the difference; this is useful to "debug" the expression with set analysis

left Sum({$<WeekNumber = {'$(vLastWeek)'}>} Sales)

right Sum({$<WeekNumber = {'$(=$(vLastWeek))'}>} Sales)

here a lot of interesting detail (variable, dse, set analysis)

A Primer on Set Analysis

The Magic of Variables

The Magic of Dollar Expansions

Set Analysis: syntaxes, examples‌

QlikView help, search for set analysis and then Set Modifiers with Dollar-Sign Expansion

and try also this

set vLastWeek = "=max({1<WeekNumber>}WeekNumber)";

Re: Variable in set expression not evaluating

Wed, 03 Jun 2015 11:50:17 GMT

Thank you. I had no idea. I appreciate your help, Massimo!

Re: Variable in set expression not evaluating

pratap6699 — Wed, 03 Jun 2015 12:07:13 GMT

Sum({$<WeekNumber = {"=$(=$(vLastWeek))"}>} Sales)