Set Analysis

In QlikView version 8.50 there is new functionality called Set Analysis. A "Set" as used in this functionality is similar to the already familiar concept of a QlikView Selection. This functionality allows you to define chart expressions that are calculated using something other than the current selection.

QlikView's Selection capability is already a powerful, flexible capability -- essentially a grouping of data defined any way you choose. With Set Analysis you can compare two or more of these groupings quickly and easily. In the past, this kind of functionality in a QlikView report usually required special handling in the loadscript and table structure. Now, the Set Analysis can be set up using chart expressions and existing data.

I'm sure that I will be writing again about the capabilities of this feature, so today I will only offer two examples of what can be done. These are simple to try out on a report so be sure to try them. Remember though that you will need new version 8.50. You can download the latest version of QlikView from the QlikTech or QlikCommunity websites.

Examples:

Imagine that you have a chart and one of the expressions shows historic inventory quantity. It might look like this:
Sum( qty_inv_hist )

Now, add a new expression next to that first one that will show historic inventory quantity but using the Previous Selection. "Previous Selection" is the selection you would get if you clicked on QlikView's Back button. The expression might look like this:
Sum( {$1} qty_inv_hist )

Note the curly braces that are part of this syntax. You now have a chart that compares data from the current selection to data from the previous selection. This is a very flexible feature. The previous selection might be: data from your top 5 sales regions, data from last month, data from last year, data from the Western states, data from regions where the new product has been introduced, data from products using blue labels, data from customers added in 2005, data from orders shipped by rail, etc. Get it? With little effort you can do kinds of data analysis you might never even have thought of before.

Now, add another expression that will be calculated using a selection stored inside a bookmark. This is what the expression might look like for a bookmark named "TB Item List":
Sum( { "TB Item List" } qty_inv_hist )

Quotes are required around the bookmark name because it contains spaces (you could also use square braces, [ ], instead of quotes). The idea is similar to the expression that used a previous selection except that now the selection used in this expression will be based on the bookmark selection no matter what the current selection is in the report. The bookmark can be changed at any time, of course, and some people might find this feature easier to control than the one based on a previous selection. Using a bookmark is appropriate too for a complex selection that involves making selections from several fields. The idea of a Previous Selection as in the example above works only for the most recent field where you made a selection. If the selection you want to use in the chart expression involves several fields then a bookmark is the way to go. To make this a little easier for report users, you could use a variable in the expression for the bookmark name and give the report users an input box so that they can choose the bookmark to use without opening up the chart expressions. Remember that bookmarks can be exported and shared even among dissimilar reports (Click here to read a little about sharing bookmarks).

The reference manual recommends that these set analysis features not be used on expressions that do calculations using fields from multiple QlikView tables. For example, an expression that multiplies historic inventory by a cost factor should not be used if the cost factor is from a different table within QlikView than the table where historic inventory is stored. Note that is is about where the fields are stored within the QlikView tables - it has nothing to do with the external source tables that were used for loading data. The manual says the results are "unpredictable" -- that's a good enough warning for me... it's like when the waiter says "don't order the fish" - you really will be better off if you take his advice and don't have fish for lunch.

If you are interested in the Set Analysis functionality it will be worth your time to read about it in the version 8.50 reference manual. Post a comment to this blog or send me an email if you think of something unusual or clever you can do with set analysis.