topic Using variables within variables in QlikView

Using variables within variables

Wed, 27 Apr 2011 18:56:26 GMT

I am trying to imbed variables within a variable to dynamically limit an average calculation to certain years in the database.

For example, when I place

=avg( {1<Year={2009,2010,2011}>} Days_Lost)

in a variable named CurrentYear, it will give me the average days lost for just the three years 2009, 2010, and 2011. I created three variables, vMaxYrR1, vMaxYrR2, and vMaxYrR3, where I place

=Max (sig_closeYr,1),=Max (sig_closeYr,2), and =Max (sig_closeYr,3)

respectively in the three variables. I then changed CurrentYear to read

=avg( {1<Year={vMaxYrR1, vMaxYrR2, vMaxYrR3}>} Days_Lost)

. This does not produce any results. Can anyone suggest a resolution to this?

I am new to QlikView and would really appreciate your help.

Thanks

Frank

Using variables within variables

erichshiino — Wed, 27 Apr 2011 18:59:21 GMT

Since you put an expression on the variable, you need to evaluate it on the set analysis to use it:

Try:

avg( { 1 <Year = {$(vMaxYrR1),$(vMaxYrR2) ... etc

Using variables within variables

Thu, 28 Apr 2011 12:05:18 GMT

Thanks, Erich. That worked. However, it now leads to another problem. When using {1}, one expects to get a full result, over-riding current selections. If I also embed the variables as illustrated above, now when current selections change, so does the value of the expression. Any hints on how to change that?

Using variables within variables

christian77 — Thu, 28 Apr 2011 12:49:02 GMT

Try the next:

aggr(

avg( { 1 <Year = {$(vMaxYrR1),$(vMaxYrR2) ... etc)

,DImensionThatYouUseInTheTable)

Using variables within variables

Fri, 29 Apr 2011 18:14:15 GMT

Thanks for the tip but it didn't work. Its too bad because now I have to hard code the year values in the expressions. I was hoping to use the max function to get a specific year and the previous year no matter what data was in the data set.

Thanks for your help.

Frank

Using variables within variables

erichshiino — Fri, 29 Apr 2011 18:28:16 GMT

Inside set analysis you can make:

Using variables within variables

Fri, 29 Apr 2011 20:16:48 GMT

Hey, Erich. Thank you. This worked out great. I wouldn't have found it without your help.

Frank