topic Re: Expression as variable not working in Set Analysis in QlikView

Expression as variable not working in Set Analysis

Wed, 04 Jan 2012 21:16:51 GMT

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.

Re: Expression as variable not working in Set Analysis

swuehl — Thu, 05 Jan 2012 00:55:42 GMT

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,'@','$');

Re: Expression as variable not working in Set Analysis

pover — Thu, 05 Jan 2012 01:08:36 GMT

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

Re: Expression as variable not working in Set Analysis

pover — Thu, 05 Jan 2012 02:52:02 GMT

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

Re: Expression as variable not working in Set Analysis

CELAMBARASAN — Thu, 05 Jan 2012 04:40:43 GMT

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

Expression as variable not working in Set Analysis

Thu, 05 Jan 2012 20:25:01 GMT

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!

Expression as variable not working in Set Analysis

Thu, 05 Jan 2012 20:26:27 GMT

Thanks a lot everyone!