Set Analysis is a great feature in QlikView that lets you, as a developer, take control over what your charts display and allows calculations that wouldn't be possible otherwise, at least not as dynamically. To understand its inner workings, we can compare it to how selections that are made using listboxes work.

With UI selections, we can say that whatever is selected affects the entire document, and all of the charts only display information associated with the set of data corresponding to those selections; plain and simple. In a way, that is basically what Set Analysis does. It restricts, predefines, or extends the set of data that charts base their calculations on. Using a set expression, we can, for instance, specify that a certain chart should perform an aggregation only based on records that meet a set of criteria in certain fields (for example, Region A and Region B from the Region field), even if the non-matching values are part of the user's selected record set. We can also use Set Analysis to expand the selections made by the user to show, for example, results of the previous year even when it has not been actually selected. For example, when the user selects the year 2012 and a table displays data for 2012 in one column and 2011 in the other.

That is the whole concept. However, it is sometimes not as simple to tell our chart to "show the numbers of the previous year/month/period" as it would be using selections. In Set Analysis, we need to specify the modified record set using an expression with the correct syntax.

These are some situations in which the use of Set Analysis is practical:

We will first work our way through the Set Analysis syntax, after that we will apply the concepts to achieve something that is a must in almost every QlikView document: Point In Time Reporting.

The details for creating a set expression are outlined in the following step-by-step procedure:

The value definition can also be a set of different values, in which case each element is separated by a comma.

Once we add the set modifiers, our set expression will be complete and have the following structure (shown as pseudocode for illustration purposes):

Sum({$<Field1 = {NewValue1}, Field2 = {NewValue2}>} [# Departures Performed])

Taking our base expression as a starting point, here are some examples:

Internally, QlikView evaluates the field value definitions in Set Analysis in the same manner as conditional expressions are evaluated (but usually faster), to determine if a specific record should be part of the calculation or not. Therefore, the same rule discussed previously about numeric versus text-based comparisons applies for Set Analysis. That rule: using a set modifier based on a text-based field (Month = {'Jan'}, for instance) is slower than using its numeric equivalent (MonthNum= {1}).

Similarly, it's also important to consider that, when defining a numeric comparison value, it shouldn't be enclosed in single-quotes (MonthNum = {'1'}) as it will cause QlikView to treat it as a text-based comparison.

Sum({$<Year = {$(vLastYear)}>} [# Departures Performed])

Sum({$<[Carrier Group] = {'$(vInterestGroup)'}>} [# Departures Performed])

Sum({$<[Aircraft Group] = {"$(vSearchAircraftGroups)"}>} [# Departures Performed])

In the previous section, we reviewed some basic examples using set modifiers with explicit (hardcoded) field value definitions. Our next step will be about making our modified record set dynamic and based on the user's current selections, that is, using a calculated field value definition. By doing so, the alternative record set will dynamically change depending on what the user selects.

To embed actual calculations into the field value definition in a set expression, we use the Dollar Sign Expansion (DSE) syntax. The final expression would be as follows:

Sum({$<Year = {$(=Max(Year))}>} [# Departures Performed])

As you can see, it is just as if we were using a variable, just with an additional equal sign and the expression itself. Here, the DSE function is to evaluate the enclosed calculation and the set expression will only use the output value in the set modifier.

If we want to get the record set corresponding to the previous year to whatever year the user selects, we would use:

Sum({$<Year = {$(=Max(Year)-1)}>} [# Departures Performed])

In this case, the DSE is first calculating the last possible year from the current selections record set. It goes back one year and the output is then passed to the set modifier as the field value definition. Anytime the user changes his selections, the set modifier is changed as well.

The same concept also works with Quarters, Months, Days, and so on. However, additional considerations need to be made for some fields. For example, if we were to use the previous calculation for the Month field, Max(Month) – 1 would not work if the user selects January. The expression would return zero, which is not a valid month. Although we can easily build an expression that returns the number 12 instead of zero (with the use of the If function, for instance), we will provide an even simpler and straightforward solution for this scenario in an upcoming section of this chapter (Point In Time Reporting).

All of the preceding examples use set expressions which have predefined field values in the element list definitions, overriding the user selections on the specified fields. However, in some cases, we will need to first take the actual record set that the user has selected and, from there, modify it by adding or removing certain values. To do that, we need to use a different assignment operator in the field-value definition, instead of the equal sign. The available assignment operators are:

Let's walk through some examples to better understand the assignment operators:

Set modifiers, the part of the set expression that is enclosed in angle brackets, can also be constructed by combining several different element lists in the field value definition. Furthermore, the entire set expression can be composed using several different set modifiers. This is accomplished using set operators.

The different set operators that can be used are:

The set operators work in a manner similar to the assignment operators described in the previous section. Let's review some basic examples:

Just as in arithmetic operations, parentheses can be used to enclose different set operations and ensure they are evaluated in the correct order.

There are two special functions that can be used in set expressions to implicitly specify an element list. The functions are:

A quick example:

The possibilities are endless. Take a moment to try it out in the Airline Operations document and see for yourself what you can do with Set Analysis. In the solution file corresponding to this chapter, we've included an additional sheet, named Set Analysis, with different examples of Set Analysis uses.