Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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.

1 Solution

Accepted Solutions
Highlighted
Not applicable

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.

View solution in original post

5 Replies
Highlighted
Not applicable

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)))
Highlighted
Not applicable

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

Highlighted
MVP & Luminary
MVP & Luminary

Try this,

     $(#OnTRR)

Regards,

Kaushik solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

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.

Highlighted
Not applicable

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.

View solution in original post