Chapter 6. Set Analysis

In this chapter, we will focus on the concept of Set Analysis and its use in Qlik Sense. We will cover the following topics:

• Cracking the syntax for Set Analysis
• Using flags in Set Analysis
• Using the = sign with variables in Set Analysis
• Point in Time using Set Analysis
• Using comparison sets in Set Analysis
• Using embedded functions in Set Analysis
• Creating a multi-measure expression in Set Analysis
• Using search strings inside a set modifier
• Capturing a list of field values using a Concat() function in Set Analysis
• Using the element functions P() and E() in Set Analysis
• Using the intersection between sets for Basket Analysis
• Using alternate states

Introduction

I will say it outright that Set Analysis is one of the most important technical features of Qlik solutions. It allows you to do things dynamically that just wouldn't be possible with the default selections you have made. Set analysis can be termed as Selection Analysis. The user tells Qlik Sense what set of records needs to be picked for calculation, which is similar to making a selection from a Filter pane or active objects. The only difference is that you define the selection inside the calculation, so that the expression can still look at the records you specified inside the Set Analysis expression even if you clear all the default selections.

 

Cracking the syntax for Set Analysis

Set Analysis is a very powerful concept in Qlik Sense. In very simple terms, each set contains a group of selected dimensional values. The sets allow users to create independent selections, other than the one being used in the active Qlik Sense objects. The aggregations inside the set are compared with current selections to get the desired results.

Note

Any set that has been created in Qlik Sense only alters the context of the expression that uses it. Unless they are referencing label names inside the same visualization, all expressions using the set syntax are independent of each other. As such, basic expressions not using Set Analysis will react to normal selections made inside the Qlik Sense document.

A Set Analysis expression consists of three main parts:

1. Set identifiers, for example, $, 1, and 1-$
2. Set operators
3. Set modifiers (optional)

A set expression is defined inside curly brackets {}. Set identifiers are separated from modifiers by angular (<>...

Using flags in Set Analysis

Set Analysis expressions tend to become overly complex when there are too many comparison sets and conditions put in place. In order to reduce the complexity, you can make use of the flags created in the script in the Set Analysis expression. The flags can be set up to be simple binary values, 0 and 1. The use of flags optimizes the performance of frontend calculations. The following recipe explores this possibility by creating flags in the script to identify On-time and Late shipments.

Getting ready

For the purpose of this recipe, we will be using an inline data load that contains shipment details for each customer. Load the following script in the Qlik Sense Data load editor:

SalesTemp: 
LOAD DATE(Date#(DeliveryDate,'DD/MM/YYYY')) AS DeliveryDate, 
DATE(Date#(ShipmentDate,'DD/MM/YYYY')) AS ShipmentDate, 
Invoiceno.,Customer,Month,Sales INLINE [ 
Invoiceno.,Customer,Month,DeliveryDate,ShipmentDate,Sales 
101,ABC,Jan,01/01/2015,29/12/2014,10000 
102,ABC,Feb,02/02...

Using the = sign with variables in Set Analysis

We can make use of variables and calculations in set modifiers. The following recipe explains the syntax for using variables for comparison in sets and how to effectively use the = sign in the dollar sign expansions.

Getting ready

For the purpose of this recipe, we will be using an inline data load that contains shipment details for each customer. Load the following script in the Qlik Sense Data load editor. Make sure that the last record in this script has the Month set to the current month and the DeliveryDate set to today's date:

Let vToday=Today ();

Sales:
LOAD DATE(Date#(DeliveryDate,'DD/MM/YYYY')) AS DeliveryDate,
DATE(Date#(ShipmentDate,'DD/MM/YYYY')) AS ShipmentDate,
Customer,Month,Volume,Sales,Supplier
INLINE [
Customer,Month,DeliveryDate,ShipmentDate,Volume,Sales,Supplier
ABC,Jan,01/01/2015,29/12/2014,100,10000,DEF
ABC,Feb,02/02/2015,25/01/2015,100,10000,DEF
ABC,Mar,03/03/2015,02/03/2015,400,12000,DEF
ABC,Apr,04/04/2015,24/01/2015,100...

Point in Time using Set Analysis

"How is this month looking compared to the last?" This is one of the most common questions asked in BI solutions. In this recipe, we will build two charts and both will compare one year to another. The first chart expression will limit the range of data and make use of the Year dimension. The second chart will not use the Year dimension but will build the year comparison directly into the expression itself.

 

 

Getting ready

For the purpose of this recipe, we will make use of an inline data load that gives yearly sales information for different fruits. Load the following data into the Qlik Sense Data load editor:

Data:  
LOAD * INLINE [ 
    Fruit, Year, Sales 
    Apple, 2013, 63 
    Apple, 2014, 4 
    Cherry, 2014, 1150 
    Cherry, 2013, 1180 
    Fig, 2013, 467 
    Fig, 2013, 374 
    Fig, 2014, 162 
    Fig, 2014, 267 
    Fig, 2014, 586 
    Orange, 2013, 10 
    Orange, 2013, 50 
    Orange, 2013, 62 
    Orange, 2013, 131 
    Orange, 2013, 145 
  ...

Using comparison sets in Set Analysis

The following chart is a stacked bar chart, a standard way of comparing separate entities. Each value that you select is displayed as a segment in each bar by year:

Using a comparative analysis allows you to group the separate selections dynamically so that you can compare them against one another. In the preceding example, we can group together Plum and Apple versus Fig and Orange.

Getting ready

For the purpose of this recipe, we will make use of an inline data load that gives yearly sales information for different fruits. Load the following script in the Qlik Sense Data load editor:

Data:  
LOAD * INLINE [ 
    Fruit, Year, Sales 
    Apple, 2013, 63 
    Apple, 2014, 4 
    Cherry, 2014, 1150 
    Cherry, 2013, 1180 
    Fig, 2013, 467 
    Fig, 2013, 374 
    Fig, 2014, 162 
    Orange, 2013, 131 
    Orange, 2013, 145 
    Orange, 2014, 102 
    Pear, 2014, 489 
    Pear, 2014, 782 
    Plum, 2013, 148 
    Plum, 2014, 412 
];  
 
DataIslandFruit: ...

Using embedded functions in Set Analysis

As you have seen in the previous recipes, we have used functions, such as Max() and GetFieldSelections() inside our Set Analysis. Embedding functions inside a Set Analysis expression, specifically in the rules area that defines the set of records we want to calculate across, is known as Dollar-sign expansion.

A set of records in the simplest literal form is defined as Year= {2015}.

The expression needs to know the year you want to use and dollar sign expansion allows us to generate the text dynamically. Understanding how to use dollar sign expansion in your Set Analysis expressions enriches the amount of analysis you can perform. Sometimes, just using the function alone or specifying literals in Set Analysis is either too time-consuming or adds unnecessary maintenance to the application.

Getting ready

For the purpose of this recipe, we make use of product sales data as defined in the following script. Load the following data into the Qlik Sense Data load...

Creating a multi-measure expression in Set Analysis

Sometimes, you may have groups of expressions you want to view that either don't need to be viewed at once, or you don't have the room to display them all. In these cases, you do not have to go and create another sheet; you can add a control to let users select what is calculated.

The output of this recipe is similar to the preceding recipe, only with slightly different expressions to add depth of analysis to the same object.

Getting ready

For the purpose of this recipe, we make use of product sales and margin data, as defined in the following script. Load the following data into the Qlik Sense Data load editor:

Transactions: 
Load  
Date(today()-IterNo()) AS Date, 
Pick(Ceil(3*Rand()),'Standard','Premium','Discount') AS 
   ProductType, 
Floor(date(today()-IterNo())) AS DateNum, 
 Round(1000*Rand()*Rand()*Rand()) AS Sales, 
 Round(10*Rand()*Rand()*Rand()) AS Quantity, 
 Round(Rand()*Rand(),0.00001) AS Margin 
Autogenerate 10000 
While Rand...

Using search strings inside a set modifier

A set modifier contains one or several field names that make up the set expression. We can define a range of values within the selection made in the set modifier. The following recipe makes use of search strings to calculate sales within a specified date range.

Getting ready

For the purpose of this recipe, we will be using an inline data load that contains shipment details for each customer. Load the following script in the Qlik Sense Data load editor:

Sales: 
LOAD DATE(Date#(DeliveryDate,'DD/MM/YYYY')) AS DeliveryDate, 
DATE(Date#(ShipmentDate,'DD/MM/YYYY')) AS ShipmentDate, 
Customer,Month,Volume,Sales,Supplier INLINE [ 
Customer,Month,DeliveryDate,ShipmentDate,Volume,Sales,Supplier 
ABC,Jan,01/01/2015,29/12/2014,100,10000,DEF 
ABC,Feb,02/02/2015,25/01/2015,100,10000,DEF 
ABC,Mar,03/03/2015,02/03/2015,400,12000,DEF 
ABC,Apr,04/04/2015,24/01/2015,100,10000,GHI 
DEF,Feb,03/02/2015,03/02/2015,200,25000,GHI 
DEF,Mar,25/03/2015,21/03/2015,300,25000,GHI...

Capturing a list of field values using a Cconcat() function in Set Analysis

While we have used the search strings in previous recipes to do a numeric search, we can also perform text searches by using the wildcard character, *. Occasionally, however, you might want to compare the values in one field to the values stored in another. We can also achieve this using Set Analysis and the Concat() function.

Getting ready

For the purpose of this recipe, we make use of product sales data as defined in the following script. Load the following script in the Qlik Sense Data load editor:

Transactions: 
Load *, 
    If(Len(TmpSubCategory)=0,Null(),TmpSubCategory) AS 
     SubCategory; 
Load * INLINE [ 
    ProductType, Category, TmpSubCategory, Sales 
    Premium,A4,A4,300 
    Standard,A4,A4,100 
    Premium,A5,A5,500 
    Standard,A5,A5,200 
    Premium,A6,A6,1000 
    Standard,A6,A6,600 
    Premium,A1,,700 
    Standard,A1,,300 
    Premium,A2,,300 
    Premium,A3,,200 
    Standard,A3,,60 
];  

How...

Using the element functions P() and E() in Set Analysis

So far, we have seen how sets can be used to manipulate the result of an expression. To take the concept a bit further, we will now see how to use the P() and E() functions inside a Set Analysis expression. In the previous Set Analysis expressions, all field values were explicitly defined in the sets or variables or, in certain cases, through defined searches. The P() and E() functions make use of nested set definitions.

 

A P() function returns a set of all possible values, while an E() function returns a set of all excluded values.

Getting ready

For the purpose of this recipe, we make use of customer sales data as defined in the following inline data load. Load the following script in the Qlik Sense Data load editor:

P_E: 
LOAD * INLINE [ 
Customer,Month,Volume,Sales,Supplier 
ABC,Jan,100,10000,DEF 
ABC,Feb,100,10000,DEF 
ABC,Mar,400,12000,DEF 
ABC,Apr,100,10000,GHI 
DEF,Feb,200,25000,GHI 
DEF,Mar,300,25000,GHI 
DEF,Apr,200,25000,ABC ...

Using the intersection between sets for Basket Analysis

In the previous recipe, you learned how to use the P() function inside a Set Analysis expression. A P() function returns a set of all possible values.

To take the concept a bit further, we will now see how to use the P() function combined with the intersection operator * to help us locate invoices where two or more products were sold together.

 

 

Getting ready

For the purpose of this recipe, we make use of product sales data by invoice, as defined in the following inline data load. Load the following script in the Qlik Sense Data load editor:

Basket: 
LOAD * INLINE [ 
BasketInvoiceno, BasketProduct, BasketSales 
101, Apple, 10000 
101, Orange, 5000 
101, Lemon, 6000 
102, Apple, 30000 
103, Orange, 4000 
103, Strawberry, 4000 
104, Lemon, 4000 
106, Apple, 3000 
106, Orange, 2000 
107, Strawberry, 4000 
 
]; 

How to do it…

1. On a new sheet, drag and drop the Filter pane object from the Assets panel on the left-hand side of the screen.
2. Add BasketProduct...

Using alternate states

The Qlik Sense associative engine allows you to work with two states of selections: {1} for all records with no selections applied, and {$} for all records with current selections applied. They are also known as identifiers.

Considering these two states, we can create set expressions that ignore all selections except those you explicitly identify in your selection.

To take the concept a bit further, we will now see how to create a dashboard with objects that only accept selections in calendar fields.

Getting ready

For the purpose of this recipe, we make use of product sales data by invoice, as defined in the following inline data load. Load the following script in the Qlik Sense Data load editor:

Transactions: 
Load  
 date(today()-RowNo()) AS Date, 
 Pick(Ceil(4*Rand()),'Apple','Orange','Lemon','Strawberry') AS  
  Product, 
 Pick(Ceil(2*Rand()),'Standard','Premium') AS  
  ProductType, 
 Round(1000 * Rand()) * Ceil(4*Rand()) AS Sales 
Autogenerate 1000; 
 
Calendar: ...