5 Replies Latest reply: Jan 29, 2014 7:01 PM by Lyndon Nelson

# Can I use a variable (or expression result) in the below() function?

Hi,

We have need to use the below() function in a straight table, but the n value as shown in example

below([ total ] expression [ , offset [,n ]])

needs to reference either a variable or a previous column in the straight table.

The exact expression we are using is:

max(aggr(rangesum(below(sum({<EXCLUDE_RECORD={''},STATION={'ON-STATION','T'},THREE_YEAR= {'-1'}>}QTY),0,OnTRR)),PARTNO,CAL_DATE))

where OnTRR is the variable I need to usee as it will be different for each PARTNO in the table. When I put 31 in place of OnTRR, the expression calculates correctly.

I have tried every possible combination I can think of with parenthesis, brackets, dollar expansion, column(), column name, etc, and nothing seems to work. I have tried searching the community as well, and didn't find any info on how to use a variable with the below() function.

Any help/ideas would be appreciated. Thanks.

• ###### Re: Can I use a variable (or expression result) in the below() function?

I used all of these calls to the other expression (in the same chart) with no luck. Any other thoughts??

 OnTRR =OnTRR \$OnTRR (OnTRR) =(OnTRR) \$(OnTRR) =(=(OnTRR)) =(\$OnTRR) =(\$(OnTRR)) \$(\$On(TRR)) \$(=OnTRR) \$(=(OnTRR)) (\$(\$(OnTRR)))
• ###### Re: Can I use a variable (or expression result) in the below() function?

The closest I have been able to come is making OnTRR a variable (no = )and using \$(=\$(OnTRR)) in the expression - BUT this takes a single value for OnTRR and uses that in the expression. Interestingly, I can simply put \$(OnTRR) in an expression and it will show the correct value for each row.

Something is flaky with using a variable in the below() function, I think.

Anyone have any ideas???

• ###### Re: Can I use a variable (or expression result) in the below() function?

Try this,

\$(#OnTRR)

Regards,

Kaushik solanki

• ###### Re: Can I use a variable (or expression result) in the below() function?

Tried that, and a bunch more. The below() or above() functions do not seem to like a variable for the third paramenter, as in below(QTY,0,x) where x is the variable. If you would, take a look at my very much related posting http://community.qlik.com/message/315915#315915 - there is an example program with data there that if you could fix, I would be ever indebted....

Thanks.

• ###### Re: Can I use a variable (or expression result) in the below() function?

I finally found a work around to this issue - not the most elegant solution but it works.

I created a working table with a key of the three dimensions composited together. Then I would loop through the chart, getting the calculated values for OnTRR and insert that into the working table via dynamic update. I then use the field in the working table in place of the OnTRR, and IT WORKS.

If anyone ever needs more detailed info, let me know.