Category Archives: Visualization

A Color Trick

A Customer showed me this stacked bar chart and asked how to “make the Goals a different color”. What he really wanted was to differentiate the Goal stacks from the Actual stacks — but still be able to associate the Channels (Consumer, Online,…).

Channel values are expected to change over time, so any hard-coding of Channel to color would require maintainence. Here’s the Background Color expression I suggested:

,argb(96,255,255,255) bitand color(FieldIndex(‘Channel’,Channel))

If that makes perfect sense to you, read no further. Otherwise let me break the expression down.

What we are doing in the expression is setting the Alpha value to “96” when “Type=Goal”. “Type=Actual” will retain the Alpha default of “255”.

Color codes in QV are made up of four numbers — Alpha, Red, Green, Blue — values which we can set in a color dialog or a color function. Alpha indicates the amount of transparency, ranging from 0 (fully transparent) to 255 (fully opaque). Colors functions without an explicit Alpha value like “RGB(0,128,0)” default to Alpha=255.

The function “Color(n)” returns the color code for the “Nth” position in the chart color palette.

FieldIndex(‘Field‘, Value) returns the position of  Value in Field (by load order).  This will generate a number for each distinct Channel value, regardless of how many Channel values are loaded. We are effectively assigning persistent colors because FieldIndex() is not affected by selections.

1. We use color(FieldIndex(‘Channel’,Channel)) to select color n from the palette.

2. If  Type=Goal  we use the boolean bitand operator to set the Alpha value for the selected color to “96” without modifying the RGB values. Adjust this 96 value for an effect of your liking.



Calculating a Duration Using Two Rows

I’m always tickled to learn something new in QV, especially when it’s simple and elegant. swuehl, a prolific and respected contributor to QlikCommunity, answered this Community post with a brilliant solution.

The problem was how to calculate a Project (the Dimension) duration using dates from two different rows in the source table. I would have automatically reached for a script solution, Generic Load. But what if you don’t want to modify the script or don’t want the flattened model produced by Generic Load?

Swuehl’s solution computed the value in the chart using Set Analysis to fetch the needed values from the appropriate row.

only({<progress = {deployed}>} entry_date ) 
   – only({<progress = {started}>} entry_date)

The cleverness lies in using the only() aggregation function. Set Analysis can only be used in aggregation functions, so he used only() to enable specifying the Set.

I like it.



Establishing a Sort Order

Sometimes a desired sort order does not follow a natural alpha or numeric sort pattern. For example, there may be project phase names that should appear in charts in this specific order:


One of the Sortby  options available in chart properties is “Load Order”.

A specific Load Order for a field can be created by loading a dummy table with the desired order prior to loading the source data. The dummy table “PhaseSort” may be dropped after loading the source data.

// Load a dummy table to establish 
// sort order for field “Phase”.

// Load the source data
ProjectId, Phase, Amount
FROM projects.qvd (qvd)

// Dummy table no longer needed



Animated Charts and Gapminder

My colleague Barry Harmsen on the QlikFix blog recently published some cool QV animations. Barry is the kid who’s math homework I copied because I was too busy playing volleyball.

Barry’s post referenced the work of Hans Rosling and Hans Rosling is one of my data visualization heroes and I use Mr Rosling’s work as demonstration material when I teach Qlikview, particularly his widely viewed “Health and Wealth of Nations” time series animation.

Inevitably, after viewing “Health and Wealth of Nations” my students would ask “Can Qlikview do that?”. My answer used to be “I’m not sure”, but some time back I set out to recreate the same animation in QV. So the answer is now “yes”, but ironically, I haven’t had a student ask since I developed it. I have to wear an anticipatory grin and prompt the students with “Do you think Qlikvew can do that?”.

For the record, here’s the QV version of Gapminder’s “Health and Wealth of Nations”.  You can download a working QVW that includes the animation here. Click the green play button and watch it march through the years from 1800 to 2009.

Qlikview chart animations are enabled on the Dimensions pane, Animate button. Animations do not play in the Ajax client.
This animation has a big wow factor, and it’s a hit in class. But I’ve yet to see a useful animation implemented with customer data. If you have done one, I’d love to hear about it.
I find the the work of Gapminder to be not only technically inspiring, but a powerful demonstration of how data visualization can foment understanding of important issues.

The Power of Gray

I’m sometimes asked by developers “How do I make un-associated (gray) data disappear from listboxes?”. After some discussion as to why they want to do this, I usually attempt to convince them to not hide any data.

Gray data is an important part of the power of Qlikview — the associative experience.  Associative is the way our mind connects data. When I try to recall a face I may narrow my candidates by an attribute like hair color or where we met. But I don’t forget everyone else. I just take them out of focus.

I recall one of my first Qlikview “Wow!” moments when I saw the power of the associative process. I was working with a Industrial Safety analyst who was studying the effectiveness of various glove styles in preventing hand injuries. We had a database of injuries and a separate dataset of glove models in use by period and location.

Up to this point, the analyst had been working from static reports that counted injuries of interest by looking for “where BodyPart=’Hand'”.

We loaded the data into Qlikview. BodyPart  became a listbox. He selected “Hand” and the table charts displayed the same counts as the static reports. In the gray data we noticed a significant number of injuries for “Hend”, a misspelling of “Hand”. A simple click added those into the total. The analyst also discovered “finger” and “fingers” were of interest and added those. Furthermore, for certain glove styles — those with a gauntlet sleeve —  “forearm” should be added.

With just a few clicks the Analyst had greater accuracy in this important work. It was the display of gray data that allowed this to happen, along with the ability to quickly convert gray to green (selected).

So while there may be edge cases where it’s necessary to hide gray data, I generally find that displaying all data in the associative manner is a unique and special advantage of Qlikview.


Initial Selections and Current Selections

I’ve posted in the past about setting “dynamic” (based on date) initial selections. The easiest method from the Developer perspective is to apply search expressions, save them in a bookmark, and apply the bookmark as an OnOpen action. The bookmark may also be selected by the user at any time to return to initial selections.

For example, to select the current year and month:


This selects the correct Year and Month. However, the current selections display is poor.  The search expression, rather than results, is displayed in the Current Selections box.

Alternatively, those same dynamic selections can be achieved via Actions assigned to the Document OnOpen Trigger. Using Actions will present a cleaner current selections view. Here are the two Actions we should assign to OnOpen to achieve the same selections — but with a better looking current selections display at the end.

The Current Selections Display will now be much more comprehensible to the user.

Can we provide a way for the user to return to the dynamic initial selections by utilizing a bookmark? Yes we can. Create a new bookmark, assign it an appropriate name (like “Current Period”) and note its ID (for example “BM02” — from the Bookmark, More menu). Add one more action to the OnOpen trigger.

The selection values will be calculated and the bookmark updated with the new values each time the document is opened. 



Correct Time Arithmetic

Each time value in Qlikview has  both an external display string value and an internal numeric value. The numeric value ranges from 0 to 1 and is calculated as time/24 hours.  For example, 12 noon is represented as 0.50. 
Rounding during arithmetic can yield time values that share the same same external string but have a different underlying numeric value. The numeric value is used to group values in listboxes and chart dimensions. This is why you can sometimes see the time “01:00” display twice in a listbox or chart dimension!
Both of the time values below will display as “8:00:00 AM”. But they will not group together in the same dimension bucket on a chart.

8/24 = 0.33333333333333
MakeTime(8) = 0.33333333334303
I’ve seen a number of methods to do time and interval calculation. Some methods will have problems with certain time values. 

Look at some examples below and how they would match up with QV times, such as those read from databases or created by QV time functions. 

MakeTime(8) =     time#(’08’, ‘hh’)     True
MakeTime(8) = 8/24 False
MakeTime(8) – MakeTime(1) = MakeTime(7) False
MakeTime(4) – MakeTime(1) = MakeTime(3) True
time#(’08’,’hh’) – 1/24 = time#(’07’,’hh’) False
time#(’04’,’hh’) – 1/24 = time#(’03’,’hh’) False
time#(’08’,’hh’) – time#(’01’,’hh’) = time#(’03’,’hh’) False
time#(’12’,’hh’) – MakeTime(3) = time#(’09’,’hh’) True
time#(’08’,’hh’) – MakeTime(1) = time#(’08’,’hh’) False
time#(time(MakeTime(8) – MakeTime(1))) = MakeTime(7) True

The only reliable and 100% consistent method is the last line. The method is: use Qlikview functions to represent times and convert the result of calculations to time strings and back to time numbers again.

Breaking down the  example: 

1. MakeTime(8) – MakeTime(1) give us a display value of  7:00:00 AM. But as you can see previously in the table, it’s internal value is not exactly equal to MakeTime(7) — the value Qlikview considers to be 7 AM.

2. Convert the value of the calculation to a string using the time() function. The result is “7:00:00 AM”.

3. Convert the string into a numeric time using the time#() function. We now have the QV value for 7 AM which is equal to 7 AM read from a database or generated from any QV time function.

The same problem and solution applies to Timestamps and Intervals.

What we really need is a new “AddTime()” function, similar to the AddMonths() function. I sometimes create a variable function in my script to perform this function.

SET AddTime=time#(time($1 + $2));

Then I can use it in script or charts as:




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.