Set Analysis in Qlikview

Why use set analysis?

In QlikView the selection you make can be considered as a set, the selection not made are another set. Using set analysis these sets can be customized to make a conceptual selection so that we display the desired values on the charts. Set analysis always involves an aggregation function.

Qlikview set analysis is a powerful way for you to create dynamic data sets mainly for comparison analysis.

 

The QlikView set analysis is based on the set theory.

A set analysis is calculated before the chart is computed and redrawn. As you may notice, it is NOT calculated per each row/column in the chart. So, you may not guess a scope of product depending on the rows that are products. You may not guess another set of periods depending of the columns if the Period dimension is in column : the YTD or moving totals you may encounter are often valid only if the period is not a dimension of the chart. The sets let you create a different selection than the active one being used into the chart or table. The created group let you compare the aggregations of this group and the one of the current selection.

These different aggregations let you compare for example the sales of the current month and those of the

previous year, create YTD or moving totals. You may also create market shares, a percentage of the

products, of the year ….

A set modifies the context only during the expression that uses it. Another expression without any

set will get the default context, standard selection or the group of the Alternate State.

A set is always written between curly braces {…}. A set can modify the selection of one or several

dimensions. It can be composed of a single set but can include also an inner set.

 A set analysis is calculated once per chart (or table). NOT once per row. If you

want a different result in your set analysis according to the row being calculated and displayed, it will NOT

work

A set may be composed of:

- An identifier

- An operator

- A modifier

These 3 fields are optional. Of course, to write a set, you will need to incorporate one or several of these

fields. 

1.  Set analysis begins and ends with { }

2. Modifier begins and ends with <  >

3. Operator:  =

4. Identifier is not shown but it is $ which is the default state in Qlikview.

For those of you who know SQL, set analysis is akin to a SELECT…. GROUP BY delivered in a concise expression. 

Identifier is like a SELECT keyword and Modifier is  like a WHERE clause or a filter in your SQL select statement. It works on the selected data set — based on your identifier — to add, remove or to modify existing selections.

 

 

Basic set analysis syntax consists of Identifiers, Operators and Modifiers.

Identifiers - Identifiers can be compared to a from statement in SQL. The identifier determines the set we will be operating on. Identifier begins and ends with the curly brackets. Following are the identifiers we can have in set analysis.

Identifier	Description
0	Represents an empty set
1	Represents the full set of all the records in the application
$	Represents the records of the current selection
$1	Represents the previous selection
$_1	Represents the next (forward) selection
Bookrmark01	Represents the bookmark ID or the bookmark name

Group – group name (Alternate State)

Sum({Book1} [Sales]) : sales of the selection of Book1

Sum({Group1} [Sales]) : sales of the Alternate State Group1 (the syntax is identical)

Sum({1} [Sales]) : sum of everything (All dimensions are completely reset to All)

Sum({$} [Sales]) : sum of the current selection (= sum([Sales])

Operators - Operators are used to operate on the identifier. Following are the operators we can have in set analysis.

Operator	Operator Name	Description
+	Union	Returns a set of records that belongs to union of sets.
-	Exclusion	Returns records that belong to the first but not the other of the two set identifiers.
*	Intersection	Returns records that belong to both of the set identifiers.
/	Symmetric Difference	Returns a set that belongs to either, but not both of the set identifiers.

Example : sum({$ * Bookmark01} Sales) returns the sum of sales that is an intersection between the bookmark and current selection.

If you use the operators with the equal sign:

: add the set to the current selection

: remove the set from the current selection

Sum({1-$} [Sales]) : sum of the sales of the « database » except the current selection

Sum({GROUP1 * Book1} [Sales]) : sum if the sales for the intersection of Group1 and Book1 (the

members belonging to both)

Modifiers - Modifiers can be compared to the Where clause in SQL. These are additional or changed selections. The modifiers should always be in angle brackets.

Example - sum({$} Sales)

This will give the sum of sales for year 2010 and region US. Note that Year and Region are modifiers in the expression.

Using p() and e()

These functions returns the members that have (or have not) data. They are based on the associative

model.

Syntax: {) [, Other

dimensions of the Set]>}

Please note that:

- Both functions p() et e() have the same syntax : one p() returns the possible values, the other e()

returns the excluded values

- They will be inserted into a greater set

- These functions do let you search the Top N products or those whose sales are greater than X :

they just return the members (ie the products) that have data, or those that have not.

Using IF statement

It may happen that we need different set analysis according to the user selection. For example, if the user

chooses one single product, we want to compute/display all of them. If he chooses several, we want to

show/compute them as they are selected.

The IF statement cannot be used within the set analysis itself. But we can use a variable … that can

contain a IF statement.

Let’s create a variable vSet that will be a formula: =if(GetSelectedCount(Product)=1, 'Product=', '$')

And we just need to use this variable in the set itself: sum({<$(vSet)>} Sales)