Advanced Set Analysis in Qlik.

hesh — Sat, 14 Jan 2023 16:53:53 GMT

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={"Chicago"}, Product-={"Lamborghini"}>} Sales}

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

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

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

Sum({ <Region ={ [Chicago] }> } 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 = {"<2022”} >}

All records where the year is less than 2022

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

All records where Sales totals are less than 100,000

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

All records where the year is between 2021 to 2022

{< 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

Re: Advanced Set Analysis in Qlik.

Akash_Dixit — Wed, 10 May 2023 05:22:15 GMT

Excellent explanation. Would you mind sharing how to display all of the column data while excluding one particular column value? say, by showing the combined sales of every nation excluding China.

All I can think of is P() and E() function... below is the expression i have used but not sure what is the issue.

sum (
{$<country =- p({$<country={'China'}>} country)>}
Sales)

article Advanced Set Analysis in Qlik. in Member Articles

Advanced Set Analysis in Qlik.

Re: Advanced Set Analysis in Qlik.