Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
Try the next:
aggr(
avg( { 1 <Year = {$(vMaxYrR1),$(vMaxYrR2) ... etc)
,DImensionThatYouUseInTheTable)
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
Inside set analysis you can make:
<Year = $(=max({1}Year))>
Hey, Erich. Thank you. This worked out great. I wouldn't have found it without your help.
Frank