Expression as variable not working in Set Analysis

[#1]

Hi all,

I am trying to integrate some variable in a set analysis, but I have difficulty to make it right.  I just wonder if anyone knows how to resolve it?

(The QlikView application was built with a lot of variables, so I have to inherit the same coding standard.)

The set analysis should be evaluated like the following at the end.  This expression works when I put it under the Expression tab on a bar chart.

sum({$ <MonthYearNum = {">=$(=num(MonthStart(AddMonths(max(EndMonth), -5))))"}>} [Total Pounds])

I tried the following but none of them worked:

SET vBack6MonthYearNum = '=num(MonthStart(AddMonths(max(EndMonth), -5)))';  (this variable will be re-used in many other set analysis)

SET vTotalPounds6MonBk1 = 'sum({$ <MonthYearNum = {">=$($(vBack6MonthYearNum))"}>} [Total Pounds])';

---> In Variable Overview it becomes sum({$ <MonthYearNum = {">=(internal error)"}>} [Total Pounds])

SET vTotalPounds6MonBk2 = 'sum({$ <MonthYearNum = {">=$(=$(vBack6MonthYearNum))"}>} [Total Pounds])';

--> In Variable Overview it becomes sum({$ <MonthYearNum = {">=(internal error)"}>} [Total Pounds])

SET vTotalPounds6MonBk3 = 'sum({$ <MonthYearNum = {">=$(=num(MonthStart(AddMonths(max(EndMonth), -5))))"}>} [Total Pounds])';

--> In Variable Overview it becomes sum({$ <MonthYearNum = {">=(internal error)"}>} [Total Pounds])

I appreciate all feedback.  Thanks a lot.

[swuehl · Accepted solution]

Try something along these lines:

Set vBack6MonthYearNum = '=num(MonthStart(AddMonths(max(EndMonth), -5)))';

Let vTotalPounds6MonBk1 = '=sum({$<MonthYearNum = {">='&chr(36)& '(vBack6MonthYearNum)"}>} [Total Pounds])';

So I need to take care that the dollar sign expansion is not done in the script, therefore I used chr(36) and Let to combine a text snippet with dollar symbol and then another text snippet. No dollar sign expansion takes place, since no dollar sign is read before the complete expression is evaluated by LET statement.

Hope this helps,

Stefan

edit:

Alternatively, you could replace the last line with these two lines, making the actual variabe definition a bit more readible:

Set vTotalPounds6MonBk1 = '=sum({$<MonthYearNum = {">=@(vBack6MonthYearNum)"}>} [Total Pounds])';

Let vTotalPounds6MonBk1 = Replace(vTotalPounds6MonBk1,'@','$');

[pover]

Try just putting sum($<MonthYearNum={">=$(vBack6MonthYearNum)"}>} [Total Pounds])

Normally, I put the whole or part of the set analysis as a variable like

set vBack6MonthYear = '{$ <MonthYearNum = {">=$(=num(MonthStart(AddMonths(max(EndMonth), -5))))"}>}'

then the formula is

sum( $(vBack6MonthYear) [Total Pounds])

Karl

[pover]

Stefan is right.

The only thing that I would add is that if you're going to organize numerous expressions as variables, I think a better way of doing it would be to define the variables in an Excel file and import the variables into QV using something like the example that Rocco posted

http://community.qlik.com/thread/27883

Karl

[CELAMBARASAN]

Hi mantingliu,

     Try adding this variable values in variable overview itself if your going to use this in charts.

     vBack6MonthYearNum = num(MonthStart(AddMonths (max(EndMonth), -5))); 

   vTotalPounds6MonBk1 = sum({$ <MonthYearNum = {">=$(=$(vBack6MonthYearNum))"}>} [Total Pounds]);

Celambarasan

[#6]

Stefan, your solution works perfectly!  The set analysis also uses the IF statement, so I need to reuse vBack6MonthYearNum multiple times.  Therefore, I also use your second suggestion (i.e. to do a replace.) 

Thanks a lot everyone!

[#7]

Stefan, your solution works perfectly!  The set analysis also uses the IF statement, so I need to reuse vBack6MonthYearNum multiple times.  Therefore, I also use your second suggestion (i.e. to do a replace.) 

Thanks a lot everyone!