Variable in set expression not evaluating

[#1]

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'

] ;

Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBengaliBelarusianBulgarianCatalanChinese (Simp)Chinese (Trad)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHebrewHindiHungarianIcelandicIndonesianIrishItalianJapaneseKannadaKoreanLaoLatinLatvianLithuanianMacedonianMalayMalteseNorwegianPersianPolishPortugueseRomanianRussianSerbianSlovakSlovenianSpanishSwahiliSwedishTamilTeluguThaiTurkishUkrainianUrduVietnameseWelshYiddish

AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBengaliBelarusianBulgarianCatalanChinese (Simp)Chinese (Trad)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHebrewHindiHungarianIcelandicIndonesianIrishItalianJapaneseKannadaKoreanLaoLatinLatvianLithuanianMacedonianMalayMalteseNorwegianPersianPolishPortugueseRomanianRussianSerbianSlovakSlovenianSpanishSwahiliSwedishTamilTeluguThaiTurkishUkrainianUrduVietnameseWelshYiddish

Text-to-speech function is limited to 100 characters

Options : History : Help : Feedback

[ramoncova06]

have you tried without the single quotes

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

[maxgro · Accepted solution]

try with

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

[caio_caminoski]

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

[ramoncova06]

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

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

[#6]

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!

[maxgro]

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)";

[#8]

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

[pratap6699]

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