Category Archives: Visualization

The Case Against Caption Icons

I avoid using Caption Icons in sheet Objects. I prefer to teach users to right-click to access the same functionality. In V10, there is also the option to use a  “Menu” caption icon that provides the same options as right-click.

Here are my arguments for not using caption icons that duplicate the functionality available in right-click.

Note: I realize there are some limitations in the Ajax client prior to V9 that may require icons. My comments are directed at V9+.

  1. The redundancy and visual clutter waste screen space and distract from the data. If you are a fan of Edward Tufte’s work, you’ll recognize this as “low Data-ink ratio” and “Chartjunk”.
  2. They deemphasize icons that may be unique and important to a specific chart. These are things you want the user to notice, for example Help or Fast Change.
  3. They force the designer to display a Caption even when a Caption is not desirable for the layout.
  4. They keep users ignorant of the full power available, leading them to believe that they are limited to the functions offered by the displayed icons. Have you ever received a change request like “Please add the export to Excel function to chart X so we can export it.”?
  5. Also on the ignorance theme, users don’t become aware of new functionality if you don’t add the new icons.
Comments and counter arguments welcome.

Deconstructing Chart Design

In Qlikview training we survey chart types and visualization principles. In this post I’ll walk through a recent Qlikview assignment and how I applied those design principles.

The problem statement is this. System A sends many thousand transactions daily to System B and requires a reasonable response time to support the business process involved. Occasionally, the response times are atypically high and this causes problems in the downstream processes. The high response times do not always appear in clumps. Engineers require identification of the specific problem periods and feedback on the success of their tuning efforts.

Qlikview’s aggregation and drill down capabilities seem ideally suited for this problem. What metric will support the monitoring and analysis goal?

Due to the large transaction count, looking at average response time is ineffective. Two hundred 50 second responses averaged with 10,000 1-3 second transactions will not stand out enough in an average. The customer also stated that having several outliers of 200 or 300 seconds were normal and could be explained by workloads or scheduled maintenance on System B.

While there may be other statistical measures that would work, we determined that tracking the 99% percentile would identify the problem periods. Other supporting measures will be displayed to provide context for the primary measure.

The primary measure is 99% Percentile (99% of the transactions response fall at or below this value). Supporting measures will be Transaction count, Average Response Time and Max Response Time.

When I approach a new chart I try to begin with Edward Tufte’s advice to  “always know what story you are trying to tell before you design the graphic”. This story is the trend of the 99% Percentile.

Here’s my first attempt at a chart using mostly defaults. When plotting values of different magnitudes (Transaction Count and Transaction Responses) I like to use the Combo chart type.

Automatic scaling causes the red “Avg Response” line to have no meaningful shape. Further, the Transaction bars have too much prominence. They are supposed to be a supporting measure to the main story.

I change the Response values to a Logarithmic scale (Properties, Axes, Log Scale) and de-emphasize the Transaction bars by making them white (Properties, Color) and with borders (Properties, Expressions, Bar Border Width=1), Here’s the result.

The Response line now has meaningful shape, but the Transaction bars are still too prominent.

For my next revision I split the axis vertically 75/25 (Properties, Axes, Split Axes, Primary=75%). This allows the top 75% of the chart space to represent the Response lines. The remaining 25% of the chart will host the Transaction bars. I change the bar colors to a muted gray (Properties, Colors) and remove the borders.  Here’s the result.

The only prominence granted to the 99% is it’s being first in the legend, which is not enough.

For my final revision I apply Stephen Few’s principle of color and hue to emphasize the key measure. I assign the 99% Response the red color to make it stand out. I de-emphasize the supporting measures by removing the lines and using only symbols (Properties, Expression, Display Options, uncheck Line, check Symbol). I leave Symbol on for 99% to make it easier for the user to get the hover popup and drill down.

Guided by Tufte’s “Data-Ink Ratio” principle, I remove axis numbers for the Transaction bars (Properties, Axes, Hide Axis) to make the chart less busy. The significance of Transactions is trend and it’s relationship to 99%, not the absolute value. Driven by the same principle, I remove the grid lines as they are unnecessary. The story and the objective is to identify spikes, not absolute values. Here’s my final chart.


Alert Questions & Answers

I received several questions regarding my post Monitoring the Reload Schedule. Surprisingly the questions were not about the monitoring solution, but rather using Alerts. I’ll summarize the questions and answers here.

Can I loop through field values in an Alert?

Not directly. But you can create multiple alerts that use either bookmarks or set analysis in the Condition to handle a known set of field values. For example create one alert for Region=US and a second for Region=Europe and each sends an email to the associated Regional Manager.

Can I use an alert to always send an email?

Yes. Just set the Condition to “-1” (without quotes). This is always true and can be used to send text “mini-reports” to your email recipients. Your email text might be something simple like:

=’Currently open tickets: ‘ & sum(OpenTicketCounter)

Or something more complex like:
=’YTD Sales are ‘
& money(
<[Invoice Year]={$(=year(today(2)))}>
Quantity * Price
, ‘$#,##0;($#,##0)’
& ‘
YTD Orders are ‘
& num(
<[Order Year]={$(=year(today(2)))}>
, ‘#,##0’

This will generate an email body that looks like:

YTD Sales are $12,014,788
YTD Orders are 167,580

You can build up complex expressions in a Text Object to get them correct and then paste to the Alert.

Can I include a chart image in an alert email?

No. If you know of a method, please leave a comment.

It may be helpful to include a url in the email that opens the Qlikview document for further analysis.

If you have Alert questions or tips, please leave a comment.



The Now() Trap

The Now() function operates differently in script and charts. This post will highlight one difference that has tripped up even experienced developers.

Our example script requirement is to extract rows of data where the “TransactionDatetime” field value is within the last 24 hours. We’ll use the “Now()” function to establish the current time. Here’s the doc from the QV Ref Guide.

now( [ timer_mode ] )

Returns a timestamp from the system clock. The timer_mode may
have the following values:
  0 Time at script run
  1 Time at function call
  2 Time when the document was opened

Default timer_mode is 1. The timer_mode=1 should be used with
caution, since it polls the operating system every second and hence
could slow down the system.
Which timer_mode option do you choose? Hmm… you want 24 hours prior to the script execution so it seems like “0” is the best option. Plus there is that scary warning about “1” slowing down the system.

The integer 1 represents a day — 24 hours — so you code a where clause in your LOAD like:
WHERE TransactionDatetime >= now(0) -1
During development, you reload several times. You examine the data selected and it seems to be working — you are getting data from the last 24 hours. It’s 4:00pm Tuesday afternoon and you promote this to the server and set a reload schedule of daily at 8:00am. A test run on the server shows the data selection is working as planned.
The document reloads on schedule at Wednesday 8:00am. A review of the app shows data going back to Monday at 4:00pm.  After Thursday morning’s reload the data range is Tuesday 8:00am to Wednesday 8:00am. What happened to the rest of Wednesday and all of Thursday?
The value for Now(0) is set at the end of script execution. When used in script, Now(0) returns the end time of the previous script execution, not the time of the current script execution.
So on Thursday morning Now(0) returns Wednesday 8:00am — the end of the last execution. That is not what we were looking for.  
In Charts, Now(0) returns the end of the latest script execution, which would be Thursday 8:00am.
Now(1) always returns the time when the function is executed — the “current” time. During development when reloads are frequent, script Now(0) is usually pretty close to Now(1) and you may not notice the difference. But when a document goes on a daily reload schedule, Now(0) is usually an entire day behind what you want!
The correct choice in script is generally Now(1), which returns the time when the function is actually executed, not a time related to previous reload.
To Summarize, use Now(1) in script,  Now(0) in charts.

If you need to establish a deterministic (consistent throughout the entire script) value for “Now” in script, set a variable at the beginning:
LET vScriptStart – Now(1);
Then use vScriptStart as the reference point in your script. It may also be used in charts as well.



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:



Effective Visual Communication

Now you own a brand new power saw (Qlikview). But do you know how to build a house?
A key feature of Qlikview is graphical representation of data. Do you have the skills to use that capability effectively? The challenges of effective visual communication are not unique to Qlikview. The principles of visual communication can be learned and there are many resources available to learn from.
An important and useful work to me has been the book “The Visual Display of Quantitative Information” by Edward R. Tutfe (ISBN-0961392142). In this book, Tufte defines and articulates many important principles of statistical graphical representation, including the “Lie Factor” “Chartjunk”, and the “Data-Ink ratio”. The principles of Chartjunk and the Data-Ink ratio ask us to consider which of these two charts communicate more effectively.

Tufte has authored several more books that expand and illuminate these themes of quality and effectiveness in graphical communication. All of the books are beautifully printed. Tufte also teaches seminars and maintains a web site at:

Another work I have found very useful is “Information Dashboard Design” by Stephen Few (ISBN-0596100167). This book applies visual communication principles from Few, Tufte and other researchers to the practical application of “dashboard design” . Few’s book analyzes the shortcomings of some poor designs and then goes on to enumerate a number of elements for effective designs. He closes the book with some examples of optimal designs.

Stephen Few also teaches public workshops. His schedule and blog can be found at

Lastly, the IBM Many Eyes project is an ongoing source of inspiration and amusement.


The design of grouped objects

I recently submitted a feature request to Qliktech requesting “grouped objects”. There are a number of issues to consider when thinking about how grouped objects should be implemented in QV.

You are probably familiar with the idea of grouped objects. It’s a feature commonly available in graphics authoring programs Select a set of objects and mark them as “grouped”. Operations such as Move or Resize then apply to the group.

The problems I’m trying to address by asking for grouped objects:

  • When two or more objects are logically related, it’s would be useful if they could be minimized or restored together.


  • It’s a common practice to layer objects upon one another to produce an effect that is not available with a single object. For example, Stephen Few’s Bullet Graph is commonly implemented in QV as a chart upon a chart. The Qliktech demo “Finance Controlling” uses this technique. Another common use case is placing a transparent text box on a chart to provide helptext, or a button to provide action. 

    Currently, if you layer objects, you also have to prohibit the user from moving or resizing the objects. If not, user move or resize operations may cause the objects to be misaligned.

If QV would let me select a set of objects and mark them as a “group”, how should the group of objects respond to various user actions?

Let’s first consider a group of two objects that are related, but do not overlap on the screen. For example, a straight table and text box. The text box contains some summary data from the table. Here’s are some possible behaviors:

  1. If the user moved any object, all objects would move as a group, maintaining relative position to one another.
  2. If the user resizes one object, all objects will resize proportionally and maintain position relative one another.
  3. If the user maximizes one object, the group will enlarge proportionally to fill the screen.

Let’s consider another case. Two overlayed bar charts are used to create a Bullet Graph. One chart also contains a button in the caption area. It’s critical that the overlayed charts maintain position and size relative to one another. The button is a fixed artifact that should not resize but must remain in the “correct” position. How should the behavior differ from the example above?

If one of the charts is resized or maximized, both charts must resize. The button should not resize. This could be controlled by use of the “allow move/resize” property of each object. If the property is checked, the object will participate in a group resize. If unchecked, the object will not resize with the group. However, all objects will maintain relative position.

What specifically, is “relative position”? I define it as the “x,y pixel distance from the nearest corner of a parent object. The parent is the object in the same group who’s corner is closest to the child object”. An object can only be relative to one parent object. The parent may change after a resize operation.

It would simplify the grouping process if the “Allow Move/Size” property were split into two separate properties — “Allow Move” and “Allow Size”. In the chart & button example, all objects would “Allow Move” but only the charts would “Allow Size”.

Your comments and improvements on this proposal are welcome.


Update 01/09/2009: What about Object “Print” and “Copy To Clipboard -> Image” functions? My proposal would be to include all objects that share a pixel with the right-clicked object.




Using the Evaluate API in Macros

A common application of QV macros is to make initial selections. These selections may be applied when the document is opened, a sheet is activated or a button is pressed. The selections usually involve some type of dynamic value, such as “today()”.

In the examples below, the VBS continuation character “_” is used to indicate the statement continues on the next line. You may type the statement on a single line if wish and omit the “_”.

A typical macro to select yesterday’s date looks like this:

ActiveDocument.GetField(“ShipDate”).Select _

It’s all VBScript, but the functions used come from two different products. The ActiveDocument…Select is from the QV API, and the Date() function is a VBScript function. To make this work, you need some knowledge of VBS functions. In addition, there may also be a type mismatch — the VBS function returns a number and the Select expects a string. Fixing that issue requires adding a CStr() or CDbl() function.

What if you want to make a more complex selection. Like the weekday for today? Or a selection that requires knowledge of the data values — max(ShipDate)?

Wouldn’t it be easier if you could use QV Expressions to define the selection expression? You can, with the ActiveDocument.Evaluate API function. Here are some examples.

ActiveDocument.GetField(“ShipDate”).Select _

ActiveDocument.GetField(“Weekday”).Select _

ActiveDocument.GetField(“ShipDate”).Select _

The Evaluate argument is any expression that can be evaluated by QV. So you can stick with the QV functions you are used to. Those functions are also more likely to produce the correct data type for your selection. And most importantly, you get easy access to the QV data — (“max(ShipDate)”).

You can even use QV search operators such as “>”. For example, to select the last 7 days:

ActiveDocument.GetField(“ShipDate”).Select _
ActiveDocument.Evaluate(“‘>’ & date(today(1)-7)”)

I hope you find this tip useful.



Using the Google Chart API inside Qlikview

I recently had a requirement to create a heat map of the US States and set about exploring if there was an easier way than creating a QV scatter plot mapped over an image.

I found the Google Chart API. With some help from the QlikCommunity forum, I got a fairly pleasing result.

The Google Chart API is provided free by Google. You pass in an http request with parameters that describe the data and desired layout, and a chart image is returned. I won’t cover the details of the API, it’s well documented at Rather, I’ll share my experience integrating it with QV.

Here’s a screenshot of my results. The map will update as selections are made in the Sales chart. The generated map is not clickable. It’s just a static image.

A working QVW of the above may be found in the Qlikview Cookbook at:

The map is a Straight Table with a single expression. The expression is the http://… string used to generate the map. The representation for the expression is set to “Image”. Thanks to Tom on the forum for showing me this technique.

The OnAnySelect document event is used to trigger a “showMap()” macro that creates the variables needed for the http string. In a production application, you would want to be more selective and use field level events on the fields relevant to the chart.

In addition to chart layout parameters, the http string contains two parameters that describe the data.

  • chld= provides the list of states
  • chd= provides the data values for the states

The States and Values are associated by ordinal position in the respective lists. I could not find a way to keep the lists in sync by using QV expressions alone. The solution was to use the Sales chart as my “data source”. The macro walks the rows of the table to build two variables — the State codes and the Sales values. Here’s a snippet of the macro code:

set obj = ActiveDocument.GetSheetObject("CH01")
' Collect the locations
locations = ""
for RowIter = 1 to obj.GetRowCount-1
locations = locations & obj.GetCell(RowIter,0).Text
ActiveDocument.GetVariable("vValues").setContent values, false

Another area where VBScript was useful was in encoding the data values as specified by the API. I chose the “simple encoding” method. The Sales values are translated to relative values within a range of single characters defined by the API. The doc provided a javascript encoding example which I converted to VBS.

The encoding algorithm requires that the maximum value of the dataset be known to properly spread the individual values across the relative range. To determine the maxvalue in the macro, I use the QV evaluate() function to “callback” to the QV expression language.

maxValue = ActiveDocument.evaluate(
“max(aggr(sum(Sales), State))” )

Producing a chart with the Google API does have some downsides. The user must be connected to the internet and the chart will render slower than a native QV chart. It also does not provide for making selections in the chart and tooltip values like a QV chart does. But I found it to be a simple solution to my requirement. I hope that someday the QV product will provide regional maps as chart types.

Update October 3, 2008: Alistair on the QlikCommunity forum has posted an example of calling the Google Chart API without using macros, which I find to be the preferred method:

The next update to the Qlikview Cookbook will include the “macro-less” technique.