Consider the chart below. The Expression for the column labeled "Net" is:
=sum(Quantity * Price) * (1-Discount)
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
Posts
7 comments:
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
Post a Comment