Advanced Qlik Set Analysis

In our previous article,  Understanding Qlik Set Analysis , we covered the basics required to understand how Set Analysis works in Qlik. But what else can be achieved with the power of Set Analysis? How can we utilize multiple modifiers effectively?

Let’s look a bit deeper into Advanced Qlik Set Analysis and how the composition of the Set Analysis Expression improves and refines your analysis output.

1. Full Composition Usage

It is an example where the complete composition of a Set Analysis Expression is utilized, including the Identifier, Operators, and Modifiers

Syntax:

Aggregation({Identifier <Dimension1 = {"Filtered Value"}, Dimension2 = {"Filtered Value"} >} field)

Example:

Sum({1 <Region={"Texas"}, Product-={"Tesla"}>} Sales}

Qlik will accept three types of Syntax for string representation ( “”, ”, []) Double Quotes, Single Quotes, OR Square brackets

Sum({<Region ={ “Texas” }>} Sales)

Sum({ <Region ={ 'Texas' }> } Sales)

Sum({ <Region ={ [Texas] }> } Sales)

Sum of motor car sales in Texas excluding Tesla. Without changing the user’s filter.

2. Composition with multiple values

There will be instances where multiple values against multiple dimensions are advantageous to be set and defined.

The composition of the setting against specific dimensions allows one to use values or properties that you wish to include or exclude.

Syntax:

{Selection < Dimension1 = {value1, value 2, …} [, Dimension2 = {value1, value2, …}] >}

Example:

Sum({$< Region={"Texas","California"}, Product = {"Tesla","Lambo","Aston Martin"} >} Sales)

We return the sum of sales in Texas and California of Tesla, Lamborghini, and Aston Martin vehicles.

Sum($< Region= {}, Product{ "Tesla","Lambo","Aston Martin" } > Sales)

The sum of sales of Tesla, Lamborghini and Aston Martin vehicles where the region field is empty is returned.

3. Search Composition within advanced Qlik Set Analysis

You can place search conditions into the composition of the Set Analysis Expression.

Syntax:

{<Dimension = {" *string or value to be searched* "}>}

Wildcard characters are used * and ?

• * = interpreted against any sequence of characters
• ? = interpreted against any single character

Multiple searches can be performed:

{< Dimension = {"*partialVal*", "fixed text", "?erm*"} >

4. Composition using the dimension of numbers

Qlik evaluates dates and timestamps as numbers; leverage over this functionality can ease date intervals when interpreted as numbers.

Making use of GREATER THAN or LESS THAN in the Set Analysis Expressions returns predefined data as specified.

Syntax:

{< Year = {"<2021”} >}   

 All records where the year is less than 2021

{< Sales= {"<100000”} >} 

All records where Sales totals are less than 100,000

{< Year = {">=2019 <=2021"} >}

All records where the year is between 2019 to  2021

{< Sales= {">=50000 <=100000”} >}

All records where Sales are between 50,000 and 100,000

5. Composition with a numerical function

Using numerical functions like MAX(), RANK(), COUNT() or AVG() within the expression is also possible.

Syntax:

<Year = {"$(=max({1} Year))"}>

 Setting to return the last year of data

Placing the identifier {1} into the max function ensures maintaining the last year, when selections are made.

Sum({<MotorVehicle = {"=rank(sum(Price), 4)<= 10"}>} Price) 

Top 10 Highest Price vehicles

Sum({<MotorVehicle = {"=rank(sum({<Manufacturer = {“Ford”}>} Price), 4)<= 10"}>} Price)

Top 10 Highest Price vehicles by Manufacturer

Up Next: Learn Understanding Qlik Qualify and Unqualify