Visualization

Reusing expressions — chart column references

11 Comments

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!

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

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

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

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

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

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

Comments are closed.