This article is a guide to set analysis modifiers. It includes an explanation of what set analysis modifiers are and examples of how to use them. Each example has a syntax. Please see the contents below for the full list of what is covered.
What are set analysis modifiers
Set analysis modifiers are part of the set expression that tells Qlik Sense how to modify the records selected by the preceding identifier. They include field names and values within those fields and are enclosed by angle brackets.
The rest of the article will showcase the various modifier strategies you can use when working with set modifiers.
How to use set analysis modifiers to include only specific values within a given set of fields
The most common way to use modifiers is to filter the data by values within a field. The general syntax is as follows:
Sum({} field_to_sum)
The modifier elements are:
- field_name_1/ field_name_n: name of fields we are filtering by the values. Multiple fields can be included, with each new modifier separated by a comma from the previous one.
- ‘value_1’/ ‘value_n’: values we are filtering the fields by. Multiple values can be entered. They must be separated by a comma and be contained within a single set of curly brackets. Note that quotes are only necessary if the values have special characters or if you wish to use wildcards (more on this below). The comparison is case-insensitive.
How to use set analysis modifiers to exclude specific values within a given set of fields
To exclude specific values in fields from the data, add a minus sign in front of the curly brackets.
Sum({} field_to_sum)
This syntax is identical to the above, but there is a minus sign in front of the opening curly brackets.
How to use wildcards in a set analysis modifier
A wildcard is a character that represents other characters.
Sum({} field_to_sum)
To use a wildcard search within a set analysis modifier, use the relevant wildcard character and enclose the value in double quotes.
How to search numerical ranges in a set analysis modifier
To search a numerical range, write the search string without spaces and surround it in double quotes.
Sum({=5<=10"}>} field_to_sum)
The syntax above will search field_name_1 for values above or equal to 5 and below or equal to 10. The same approach also works with dates. For example:
Sum({=01/01/2024<01/02/2024"}>} field_to_sum)
This syntax searches field_name_1 for dates above or equal to the 1st Jan 2024 and below the 1st Feb 2024.
How to use set analysis modifiers to search for values calculated using a function
Sometimes, you may want to create a set analysis expression that searches for some calculated value rather than an existing value.
Sum({} field_to_sum)
The expression word is to be replaced by your expression. For example, the set analysis below will sum the revenue only where the value within the field ‘Year’ matches the max ‘Year’ in the dataset.
Sum({} [Revenue])
How to use set analysis modifiers to search for values stored in variables
A variable can be used within the modifier to search for rows that match its value like so:
Sum({} field_to_sum)
In the above expression, field_name_1 will be filtered to only values that are passed via the variable variable_name. The field_to_sum will then be summed up.
How to use set analysis modifiers to return values based on possible values in another field
You can use the set function P() to return only values that are possible given values in another field.
Sum({} field_name_1)>} field_name_1)
The set expression above will narrow field_name_1 down to only those values that are possible given selections in other_field being made to value other_field_value. Essentially, if as a user you were to apply a selection for other_field_value in field other_field, whatever values are now possible for a selection in the field field_name_1 are being returned. That’s what the set analysis returns without the user making those selections.
How to use set analysis modifiers to return values based on excluded values in another field
The same logic can be used with the set function E() to return only values that are excluded given values in another field.
Sum({} field_name_1)>} field_name_1)
The set expression above will narrow field_name_1 down to only those values that are excluded given selections in other_field being made to value other_field_value. Essentially, if as a user you were to apply a selection for other_field_value in field other_field, whatever values are now excluded (greyed out) for a selection in the field field_name_1 are being returned.
