Understanding Qlik Set Analysis

Understanding Qlik Set Analysis

Let’s define Set Analysis in Qlik.

Set Analysis uses an expression syntax to aggregate measures based on a defined condition.

You can use it to restrict a selected value to defined parameters, regardless of the User’s selection.

The Set Analysis consists of:

• Identifier  sets the state of the User’s filter against Set Analysis.
• Operator operators refine the data set by reclassifying the data returned using single or multiple identifiers, which produce a subset or superset of the data.
• Modifier , You can add a modifier or modifiers to the set expression to change the selection. These fields are optional, and they make up the Set Analysis.

Here is the set analysis in its simplest form:

Sum( {$<Year={2015}>} Sales ), in which {$<Year={2015}>} is a set expression.

When to use Set Analysis?

Restrict selection to specific predefined dimensions
Point In Time Reporting
Comparing Time Periods
Excluding Values

Understanding Set Analysis Components

1. Identifiers:

These identifiers make provision for anticipated user selections.

1 No User Filters Applied

$ Current User Filters Applied

$1 Previous Selection ($2 two previous selections) ($3 three previous selections)

$_1 Next Selection

BM01 Use any BookmarkID or Bookmark Name

Examples:

Sum({1} Sales); returns a full set of data irrespective of user selections

Sum({$} Sales); returns the equivalent of no set analysis with User applied filter

Sum({$1} Sales); returns sales for the previous selection

Sum({BM01} Sales); returns sales for the bookmark named BM01

2. Operators:

+ Union of a set

* Intercession of a set

– Exclusion of two sets

/ Symmetric difference (XOR) of the two sets

Examples:

If you use an operator with an equals symbol:

<dimension += {”value”}> :Sets the selection to the value defined for this dimension

<dimension -= {”value”}> :Fix the selection excluding the value defined for this dimension

<dimension = >: A defined dimension without a result means that the User does not change it

For initial selection, you must enter the identifiers where $ is the default used in Qlikview. However, only the value defined in {*} or {“*”} is omitted

3. Modifiers:

The modifier is everything inside the inner tags  <> , giving an infinite possibility of filters available in Qlikview.

Understanding the basics – Steps to follow when writing Set Analysis

1. Choose the Aggregation: SUM(), AVG(), COUNT(), MIN(), MAX()
2. Choose which measure to be aggregated (Sales, Stock)
3. Set the Modifier or  Define the condition  with detail between curly brackets {} This is another Expression Syntax that will include or exclude certain records based on our condition.
4. Choose Identifier (Or $ by default if omitted)
5. Create the Set Modifier between the tags <…> Use the field name, an equals sign, and another set of curly braces to define conditions.

<[Sales Region]={“Texas”}> 

Sum{$ <[Sales Region]={“Texas”}> } Sales)

<Dimension1 = {"Filtered Value"}, Dimension2 = {"Filtered Value"} >

Examples:

Sum{$ <Year={2020,2021},Region={US}> } Sales) 

Sum{$ <[Sales Region]={“Texas”, “California”}> } Sales)

So now that we have an understanding of Qlik Set Analysis and how to apply multiple modifiers. It is possible to modify the selection in one or several dimensions at the same time. We can take the step into advanced Qlik Set Analysis, increase selectability in any aggregation.

Up Next: Learn Qlik Date Conversion for SAP