Archive for category QlikView
Commenting out an entire Load or Select statement
Rather than using double slashes (//) on all lines of a Load or Select statement or opening and closing comments (/* */) before and after, typing rem comments the script until it reaches a semicolon.
For instance:
SalesFact:
LOAD Date,
CustID,
Amount,
Quantity
FROM Sales.qvd (qvd);
…becomes…
rem SalesFact:
LOAD Date,
CustID,
Amount,
Quantity
FROM Sales.qvd (qvd);
Axis Group is hiring experienced QlikView consultants
Posted by Mike S in Announcements, QlikView on March 23rd, 2010
QlikView Consultant (Berkeley Heights, NJ/Atlanta, GA)
We are also hiring for entry-level positions and summer internships, so please pass this along to bright, young people you know who are pursuing IT-related degrees and might have an interest in Business Intelligence and Data Warehousing.
We are hiring for other positions, as well, related to different verticals and software partners. Here is the full list.
Again, please pass this along to anybody who you think may be interested, and feel free to contact me with any questions. Thanks.
Book Review: Now You See It
Posted by Mike S in Business Intelligence, QlikView, Reading on November 2nd, 2009
At the beginning of June, Stephen Few released Now You See It: Simple Visualization Techniques for Quantitative Analysis, his first book since 2006’s Information Dashboard Design. Divorced from the strict context of dashboards, it focuses on fundamental techniques for presenting data for analysis. Here is his description from the book cover:
Before you can present information to others, you must know its story. “Now You See It: Simple Visualization Techniques for Quantitative Analysis” teaches simple, fundamental, and practical techniques that anyone can use to make sense of numbers. These techniques rely on something that almost everyone has—vision—using graphs to discover trends, patterns, and exceptions that reside in quantitative information and interactions with those graphs to uncover what the discoveries mean.
Although some questions about quantitative data can only be answered using sophisticated statistical techniques, most can be answered using simple visualizations—quantitative sense-making methods that can be used by people with little statistical training. Until “Now You See It,” no book has taught the basic skills of data analysis to such a broad audience and for so many uses, even though the need is huge, critical, and rapidly growing.
For starters, this book is HUGE, with a larger footprint than Tufte hardcovers and nearly thrice the thickness of Information Dashboard Design, so do not order it, expecting to throw it in your laptop case to take your next trip. I actually laughed when I opened the box from Amazon.
It is organized into large sections on Building Core Skills for Visual Analysis and Honing Skills for Diverse Types of Visual Analysis, with a short section at the end for Further Thoughts and Hopes. The first section is like an extended introduction to data visualization vocabulary, concepts, and patterns, while the second digs into different types of analysis, like time series, part-to-whole, deviation, distribution, and correlation. The structure is logical, and the book flows well, as a result.
Every chapter is beautifully presented and rich with examples that both illustrate Few’s points and help you remember them. Absent the emphasis on dashboards, he has the opportunity to delve deeply into visual representations that are not necessarily well-suited for the precious real estate of executive information systems. So if you have read IDD, don’t worry - there is not a lot of repeat information.
It’s likely that several of the techniques will jump off the page as being applicable to data you have been studying for a long time, but you just have never thought to look at them in the ways described in the book. As you read, it’s difficult to resist the temptation to go to your computer to play, spinning your data to look at them differently.
As you have no doubt heard Few opine, many popular Business Intelligence tools do not possess the out-of-the-box capabilities to present data how he would like, so it was fun to read about some slightly more unusual chart types and figure out how to create them in BI applications that I use. At this point, I don’t think any single piece of software can be expected to encompass every possible representation, though most of the examples in the book can be approximated in Excel.
QlikView whisker plot
I strongly recommend Now You See It to anybody for whom analyzing data is a part of their jobs. I finished reading it months ago, but can still list, off the top of my head, several lessons and ideas I got from the book. Though some of the topics discussed, like geo-spatial analysis, may not be relevant for the data you use at work or the type of analysis you are capable of conducting with your current collection of tools, I can carry on a fifteen-minute conversation about something as universal as usage of bar charts.
For a more representative preview, Few has published an excerpt from Chapter 5, Analytical Techniques and Practices, on his site:
Solutions to the Problem of Over-Plotting in Graphs (PDF)
Several other Visual Business Intelligence Newsletters from 2008 and later also contain lessons and examples that appear in the book.
Bonus: A Graph Design I.Q. Test has been added to the Perceptual Edge site. If you read that site or Few’s books - or even this blog - you should do well.
QlikView mentioned in latest Stephen Few paper
Posted by Mike S in Business Intelligence, QlikView on October 30th, 2009
Fundamental Differences in Analytical Tools: Exploratory, Custom, or Customizable (PDF)
Excerpt from the September/October 2009 Visual Business Intelligence Newsletter, by Stephen Few, linked above:
Customizable Analytics Requirements
To build custom analytical applications, you need programming power. The tool ideally exhibits the following characteristics:
• Provides the means to develop an application that supports precisely what’s needed in the most effective way possible. This requires a high degree of programmability, both in terms of power and flexibility.
• Provides ready-made libraries of useful functions that can be easily plugged into the application with much less effort than it would take to build them from scratch.
• Easy and efficient to use by those who develop the applications.
• Provides the means to remove everything from view in the ? nished application that isn’t needed.
• Provides the means to guide the analyst step by step through the process.
• Provides the means to coach the user through the process with instructions and examples, as needed.
One of the products that I’ve seen that seems to do this fairly well is QlikView. You don’t need to be a professional programmer to work with QlikView. Most of what you need exists as ready-made widgets (for example, particular charts with built-in functionality) that can be easily plugged into the developing application and much of the customization is done by selecting the appropriate parameters from lists that are found in dialog boxes. Programming code might need to be written, but it’s the exception, not the rule.
When you’re developing a custom analytical application, you don’t mind wading through lists of parameters in dialog boxes or writing a little code. Unlike the process of analysis itself when you must remain immersed in thinking about the data without distraction, these steps are less disruptive to developers. Although even developers benefit from programming interfaces that keep them focused on the task at hand, what they need most is the ability to do everything that’s needed, precisely and efficiently. Writing code in this case isn’t a distraction, it’s the task itself.
Tools such as QlikView are often handy because they have much of the infrastructure that is often needed for data analysis built right into the product, relieving us of the task of creating it, which in some cases would be virtually impossible. For example, QlikView includes a powerful in-memory management infrastructure that makes it possible for data to be manipulated at extremely fast speeds. This is powerful, because when you move a slider control to filter 100,000 rows of data or you drill from the country to the state level, you want the results of that action to appear without delay.
Please check out the rest of the paper, or subscribe to Stephen Few’s newsletter here.
Another Enterprise win for QlikView
It seems as though QlikView recently scored a big sale with Google, if the number of recruiters pinging me with “an excellent opportunity for you with Google” is any indication. (Sorry, I’m not local to Northern California.)
Kudos to QlikView for continuing to make inroads with large, Enterprise customers.
The book on trellis charts, AKA small multiples
Posted by Mike S in QlikView, Visualization on July 26th, 2009
From pg. 67 of Edward Tufte’s Envisioning Information:
At the heart of quantitative reasoning is a single question: Compared to what? Small multiple designs, multivariate and data bountiful, answer directly by visually enforcing comparisons of changes, of the differences among objects, of the scope of alternatives. For a wide range of problems in data presentation, small multiples are the best design solution.
What are small multiples? Essentially, a small multiple is a series of displays with the same design structure repeated for all the images, arranged in a grid. That means each graph in the series should be the same size and shape, with the same scale, differing only in the data they display.
What are the advantages of using small multiples? On page 29 of Envisioning Information, Tufte says, “An economy of perception results; once viewers decode and comprehend the design for one slice of data, they have familiar access to data in the other slices. As our eye moves from one image to the next, this constancy of design allows viewers to focus on changes in information rather than changes in graphical composition. A steady canvas makes for a clearer pictures.” (If you want to learn more about small multiples, all of chapter four is dedicated to them.)
Unfortunately, most of Tufte’s examples in Envisioning Information, e.g. the proper formation of capital letters, light signals for a train, or Saturn’s orbit, while instructive, are a bit of a stretch to apply to common BI situations. Enter Stephen Few, who always manages to apply Tuftean principles in a way that you can use them at work. From page 159 of Information Dashboard Design:
Concerning their efficiency, a small multiple offers another advantage over a series of individual graphs: the title, legend, and other metadata need to be printed only once to represent the series.
Here, Few uses small multiples to introduce another dimension to the standard grouped bar chart:
Few also offers small multiples up as a method of resolving overplotting in graphs (PDF) (this paper is an excerpt from his excellent new book):
The last example from Stephen Few I’ll mention is that small multiples can be used as “visual crosstabs”. (Of course, it is helpful to have the supporting information available, too, if possible.)
Improve Your Vision (PDF)
A white paper worth reading that covers some of the principles involved when employing small multiples is Three Blind Men and an Elephant: The Power of Faceted Analytical Displays (PDF).
Looking at other examples, the written-in-stone-reliable (cough) Wikipedia’s sample image in the entry for small multiple is not a small multiple, due to the different metrics and scale of vertical axis in each chart.
Another small multiple fail is way back in one of the first posts on this blog, The Trilogy Meter. The problem there is that the graphs are arbitrarily arranged, while they should be in order of magnitude from greatest overall to worst overall trilogy.
My image sample from the QlikView 9.0 Beta - QlikView 9 being the first release to support trellis charts - may not have been a great example of when to use small multiples. They should not be frivolously used in place of every multi-series line graph, containing the same information in merely five times the space. That said, it may be of value if you see small multiples as an alternative to using a list box to toggle through slices of a dimension, due to the way the human memory works, as discussed in the “single context” section of my post about facilitating comparison.
Qlock
This is the what happens when I’m bored on an airplane with nothing to read:
![[qlock image]](http://blog.axc.net/wp-content/uploads/2009/06/qlock.jpg "qlock")
You can’t see this actually moving, but rest assured that the second hand ticks around the face of the clock, the minute hand increments between the small tick marks based on how many seconds have elapsed, and the hour hand increments between the large tick marks based on how many minutes have elapsed.
Can anybody guess the components and formulas I used? I’ll hide the answer for anybody who wants to try it.
Pleasant new visualization defaults in QlikView 9.0
Posted by Mike S in QlikView, Visualization on May 31st, 2009
Word is that QlikView consulted with Stephen Few for improvements to data visualization in QlikView 9.0. He was less than kind to BI software companies, including QlikTech, during his keynote at Qonnections 2008. Do you think he had anything to do with the new default colors and settings? They’re refreshingly simple and far less distracting than QlikView 8.5 and earlier’s delicious-looking captions and old-fashioned stick candy bar graphs, treading farther into Tableau territory.
9.0:
8.5:
Coincidentally, Few on small multiples (AKA trellis charts), from pages 159-160 of Information Dashboard Design:
An intelligent organizer for small multiples built into the software would allow you to reference the data, indicate which variable goes on which axis of the graph, which should be encoded as lines of separate colors, which should be arranged per graph, and finally whether you want the graphs to be arranged vertically, horizontally, or in a matrix; the organizer would then handle the rest for you. As of this writing, I have yet to see dashboard software that makes this easy to do. I reserve the hope, however, that this will soon change.
That pretty much nails how they work in 9.0.
Mmm…
QlikView 9.0 officially released today
The general availability release can be found here, though it seems the server is swamped at the moment (and didn’t particularly like Firefox): http://www.qlikview.com/download/
I feel relieved to finally install it locally and know that I can start building real applications using the new features rather than running the beta versions on a VM.
See here for an overview of some features and here and here for a few screenshots of the new chart types.
The book on sparklines
Posted by Mike S in Business Intelligence, QlikView, Visualization, Xcelsius on April 26th, 2009
Sparklines, a term coined by Edward Tufte, are becoming increasingly popular in Business Intelligence software. Some applications, like Excel (through various add-ins) and QlikView (starting in version 9.0), have the ability to make them, out of the box, while they can be created elsewhere, like Xcelsius, with a bit of creativity.
You’ve likely seen them before, but do you know when it is appropriate to use them? They’re not to be thrown around just because all of the cool data visualization kids are using them.
The background, from Wikipedia:
The term ‘Sparkline’ was proposed by Edward Tufte for “small, high resolution graphics embedded in a context of words, numbers, images.” Tufte describes sparklines as “data-intense, design-simple, word-sized graphics“. Whereas the typical chart is designed to show as much data as possible, and is set off from the flow of text, sparklines are intended to be succinct, memorable, and located where they are discussed.
The clearest and most instructive examples, not surprisingly, can be found in one of Tufte’s books, Beautiful Evidence.
Pictured components
- Line representing the last n data points
- Data point for most recent reading highlighted in red
- Value of most recent reading in corresponding red type
- Name of metric
- Acceptable/normal range as gray, shaded area
Another example of his incorporates lows and highs over the period represented:
(Note that, while the horizontal axis is not labeled, the 12 months header indicates the time period being displayed.)
There isn’t a single pixel wasted on meaningless or redundant data, embodying Tufte’s data-ink ratio. Another way in which he is practicing what he preaches is that all of the data related to each metric is in close proximity, not requiring repeated references to scattered information. Of course, those are Tufte’s specs, and different BI companies and the people who have created custom sparkline components may choose to implement them differently.
If you’re looking for guidance on the best way to apply them in your applications, I like how Stephen Few succinctly puts it: “Think of them as an enhanced, much more informative substitute for the trend arrows that often appear on dashboards.”
For only marginally more space than a trend marker, sparklines provide significantly more information and paint a more complete picture than simple up/down or green/red indicators. The lack of context surrounding trend indicators leaves open the possibility that a positive indicator represents a minuscule uptick at the end of a significant and long-term drop. In other words, when you look at your dashboard for the day and see a green, up arrow for margin %, that means margin % has improved in the most recent period, while it could still be down for the week, month, quarter, or year (Few explains something similar on page 140 of Information Dashboard Design).
While the line obviously represents some period of time, the horizontal, dimensional axis is not labeled. In fact, neither axis is. The reason is that sparklines are meant to show trends and comparisons, not detailed values, like standard line graphs. This helps explain why they are not a substitute for the standard line graph, which can more easily compare multiple dimensions or multiple measures with greater precision.
And don’t forget that the line chart is but one type of sparkline. This image from Juice Analytics shows a catalog of examples from one Excel add-in (some of which are at least mildly objectionable, in my opinion):
Finally, see this thread on Edward Tufte’s message board for the single longest conversation about sparklines since the dawn of time.
