This article is a guide to set analysis operators. It includes an explanation of what set analysis operators are and examples of operators available. Each example has a syntax. Please see the contents below for the full list of what is covered.
For the rest of this article, assume that we are running a small blog and are trying to build some KPIs focusing on revenue earned within certain years and from certain countries.
What are set analysis operators
Set analysis operators are part of the set expression that tells Qlik Sense whether the records selected should be included or excluded during the calculation, i.e. they perform operations on the records identified by the identifiers. Operators can be used with identifiers and modifiers. Therefore, the location of the operator within the syntax will vary depending on which part of the expression it’s used in.
The rest of the article will showcase the various operators available and how to use them with identifiers and modifiers.
The add operator: +
The add operator allows you to specify more than one set of records. Qlik will calculate the expression over those records that fit into any set. You can think of it as an ‘either-or’ operator, i.e. records in either or both sets.
Using the add operator with an identifier
We want to create a KPI that shows the ad revenue made using the selection returned by a bookmark, as well as the current selections. Assume we have a bookmark named ‘Country: UK’, which filters the app down to traffic from the United Kingdom. The syntax is as follows:
Sum({$+'Country: UK'} [Revenue])
The identifier $ tells Qlik to look at records returned by the current selections. The identifier ‘Country: UK’ tells Qlik to look at records returned by the bookmark ‘Country UK’. As a result, the KPI calculates over the records which fall into the current selections made by the user plus the records which would be returned if the selection of United Kingdom was made. The result is as follows:
- No selections are made: £59.49 – which is the total ad revenue. Because no selections have been made, the whole dataset is to be used for the calculation.
- A filter for the United States is applied: £55.04 – the ad revenue from the United Kingdom (£13.33) plus the revenue from the United States (£41.71).
- A filter for the year 2024 is applied: £29.17 – the ad revenue from 2024 for traffic from all countries (£20.42) plus the revenue from the United Kingdom for all years (£13.33) minus the overlap in records, which is United Kingdom ad revenue in 2024 (£4.58).
Using the add operator with a modifier
See Set Analysis Modifiers for more information on modifiers specifically. In this example, we want to create a KPI that would return the ad revenue for 2024 from all countries as well as the revenue for all years from the United Kingdom. The syntax is:
Sum({+<[Date.Calendar.Year] = {2024}>} [Revenue])
The two modifiers (the stuff between the angle brackets) are separated by the add operator, which tells Qlik you want the results of both sets.
The result of this is £29.17. Notice how the result is identical to the third selection variant of the example above.
The minus operator: –
The minus (or exclude) operator allows you to specify whether to exclude a set of records. You can think of it as a ‘not in’ operator, i.e. records not in this set.
Using the minus operator with an identifier
Assume we want to show a KPI that shows the ad revenue for all records, excluding any current selections. We can combine the ‘all records’ identifier of 1 and the ‘current selections’ identifier of $ with a minus operator. The syntax is as follows:
Sum({1-$} [Revenue])
The syntax tells Qlik to take all records and remove records returned by the current selections before evaluating the expression.
The results are as follows:
- No selections are made: £0 because current selections equal total records when no selections are made, and therefore all records are excluded.
- A selection for the United Kingdom is made: the result is £50.08 because the total revenue is £63.94 and the revenue from the UK is £13.87 (one penny difference is due to rounding – the revenue earned has more than two decimal places).
Using the minus operator with a modifier
See Set Analysis Modifiers for more information on modifiers specifically. In this example, we want a KPI that will return ad revenue from 2023 from all countries apart from the United Kingdom. In this example, we can make the exclusion within the same modifier (between the same set of angle brackets).
Sum({<[Date.Calendar.Year] = {2023}, Country = -{'United Kingdom'}>} [Revenue])
The result is £30.33.
Here is another example of using the minus operator with a modifier.
Assume we want a KPI that shows the ad revenue from 2023 but excludes the revenue from the United Kingdom earned in September only. The syntax for this would be:
Sum({<[Date.Calendar.Year] = {2023}>-} [Revenue])
The result is £37.42.
Note how the month and country restrictions are both inside a second modifier. The first modifier finds all records in 2023 while the second finds UK records in Sep. The second set of records is then removed from the first. If you tried to use the first syntax option and put the month restriction into a single modifier, ALL September records would be removed, not just ones for the United Kingdom.
The intersection operator: *
The intersection operator returns records that belong to both sets. You can think of it as an ‘and’ operator, i.e. records that fit in both of these sets only.
Using the intersection operator with an identifier
Assume we have a bookmark called ‘Country: UK’ for the United Kingdom. We want to show the ad revenue for records that fall into that bookmark AND the current selections. The syntax is:
Sum({$*'Country: UK'} [Revenue])
By using the star key, we are telling Qlik to compare the records returned by the current selections and the bookmark ‘Country: UK’ and only calculate the expression over the records that fall into both criteria. The results are as follows:
- No selections are made: £13.87 is returned. This is the total ad revenue from the United Kingdom
- A selection is made for the year 2023: £8.74 is returned. This is the ad revenue from the United Kingdom in 2023
Using the intersection operator with a modifier
Assume we want a KPI that returns the ad revenue from the United Kingdom in 2023. The syntax using the intersection operator is as follows:
Sum({*<[Date.Calendar.Year] = {2023}>} [Revenue])
The result is £8.74.
Note that you could achieve the same result by writing the criteria inside the same modifier in this example.
The difference operator: /
The difference operator returns records belonging to either set but NOT both sets. You can think of it as an ‘or but not both’ operator, i.e. records in set 1 OR set 2 but NOT in both.
Using the difference operator with an identifier
Assume we have a bookmark for the United Kingdom called ‘Country: UK’. We want to show the ad revenue for records that either fall into that bookmark or fall into the current selections but do not fall into both. The syntax is:
Sum({$/'Country: UK'} [Revenue])
In this syntax, Qlik checks the records returned by the current selections and the bookmark. Any records that fit into both criteria are removed before the expression is evaluated. The result is as follows:
- No selections are made: £50.08. This is the total ad revenue (£63.94), EXCLUDING the revenue from the United Kingdom (13.87). Note that the penny difference is due to rounding.
- A selection for the year 2023 is made: £35.45. This is the ad revenue from 2023 (£39.07), EXCLUDING the ad revenue from the United Kingdom in 2023 (£8.74), INCLUDING the ad revenue from the United Kingdom in years other than 2023 (£5.12).
Using the difference operator with a modifier
Assume we want a KPI that returns the ad revenue in 2023 from all countries apart from the United Kingdom plus ad revenue from the United Kingdom in other years than 2023. The syntax to achieve this using modifiers would be:
Sum({<[Date.Calendar.Year] = {2023}>/} [AS Ad Revenue])
The result is identical to example 2 above: £35.45.
