Reusing expressions — chart column references

It’s often useful to avoid repeating lengthy expressions. Here’s one technique for reusing expressions.

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

Share

11 thoughts on “Reusing expressions — chart column references”

  1. Hi Rob, did you know that you can use an expression like: column(1) – column(2)? Really useful if your label is an expression!

  2. 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!

  3. 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!

  4. 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

  5. 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

  6. 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

  7. Is there any way to use an expression from a different chart/object?
    Thanks!

Comments are closed.