It’s often useful to avoid repeating lengthy expressions. Here’s one technique for reusing expressions.
Let’s add another column for “Commission”, defined as 10% of Net. Instead of repeating the Net calculation, use the expression:
=Net * .1
Qlikview allows referencing the value of another column in the same chart by name. The name is the value assigned to the column label. “Net” in this case is not a Field. It is a symbolic reference to an expression. It may be used in other expressions in the same chart, but not in places where a Field name is expected.
=Net / 2 Allowed
=above(Net) Allowed
=Sum(Net) Not allowed
Column references can be very useful in Expression attributes. Let’s bold any Commission value over $200. We’ll use a Text Format expression of:
=if(Commission > 200, ‘<B>’)
giving this result:
-Rob
Hi Rob, did you know that you can use an expression like: column(1) – column(2)? Really useful if your label is an expression!
Hi Matt,
Thanks for adding that point.Be warned that if a user drags a column to a new location, the column(n) will no longer be correct.
Wow. You never cease to amaze me. You manage to find really great features in QlikView. Sometimes I really wonder how you manage to figure out these things… Most of the stuff you post is not documented anywhere else. Thanks for sharing it!
Hi Rob!
Great Blog… I have one question aboyt this specific post: Is there a way to reference to an expression that has an expression as a label in the same chart? How would you do it?
Thanks!
Mike,
If the column uses an expression for label, you can reference the column as Matt suggested above using:
column(n)
Hi Rob, good stuff – is it possible to make a reference to itself w/o using the column/expression name?
I use the chart column reference a lot for the background color of an expression. Now when I change the expression label I need to change the whole formula. Thanks, Lukas
Hi Lukas,
There is no “this” self reference. About the best you can do is use the column(n) reference.
-Rob
Hi Rob ,
Nice article.
My situation is , i have to change the background color of another expression based on reference item without displaying in the chart.
So in the context of your example , color of commission based on net value limit , but hide the NET field. But when I am doing so & hiding NET, background color doesn’t work. Is there a way ?
Thanks,
Pooja
Hi Pooja,
I’ve tested what you posted, and it seems to work for me. Can you post a question and example qvw over on the QlikCommunity forum?
-Rob
Is there any way to use an expression from a different chart/object?
Thanks!
No, you cannot reference an expression column from another chart.