Saturday, February 20, 2010

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:


Monday, February 8, 2010

Setting Initial Selections in Version 9

A common Qlikview requirement is that when a user opens a document, some type of dynamic selection -- like current date -- should be made.

In QV 8 this entailed assigning a macro to the Document OnOpen event and using macro statements similar to this:

ActiveDocument.GetField("Year").Select ActiveDocument.Evaluate("year(today(1))")

The Actions feature of QV 9 has eliminated the need to use macros for initial selections. In V9, one or more Actions can be assigned to the Document OnOpen trigger. The actions may be "Select In Field" using a expression like =year(today(1)).

A very modular way to make Initial Selections in V9 is to make all the selections you require using search expressions and then save those as a Bookmark. The Bookmark can then be applied as the OnOpen Action.  For example, here are selections reflecting current Year, Month and Weekday using search expressions.