Using variables within variables

[#1]

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

[erichshiino · Accepted solution]

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

[#3]

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?

[christian77]

Try the next:

aggr(

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

,DImensionThatYouUseInTheTable)

[#5]

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

[erichshiino]

Inside set analysis you can make:

<Year = $(=max({1}Year))>

[#7]

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

Frank