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

[#1]

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.

[#2]

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)))

[#3]

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???

[kaushiknsolanki]

Try this,

     $(#OnTRR)

Regards,

Kaushik solanki

[#5]

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.

[#6 · Accepted solution]

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.