This article is a guide to set analysis identifiers. It includes an explanation of what set analysis identifiers are and examples of the most common identifiers. 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 identifiers
Set analysis identifiers are part of the set expression that tells Qlik Sense which records to include when performing the aggregation, i.e. it identifies the records required. The identifier goes after the curly bracket but before the angle brackets, which enclose the modifiers:
Sum({*identifier*<[Date.Calendar.Year] = {2023}>} [Revenue])
In the syntax above, the desired identifier should replace the *identifier* part.
There are many identifiers to choose from, each with a different purpose. We will explore them below using an example.
The ‘current selections identifier’: $
Including an identifier is optional. If you don’t include one, the ‘current selections identifier’ is assumed. This means Qlik will filter the records returned by the current selections down by the specified set expression. For example, we want to create a KPI that shows ad revenue in 2023 but is responsive when the user makes a selection, such as a country from which the revenue is earned.
Sum({$<[Date.Calendar.Year] = {2023}>} [Revenue])
In 2023, our blog earned £39.07 through ad revenue. Therefore, the results of this KPI in various scenarios are:
- No selections are made: £39.07 – total ad revenue in 2023.
- A filter is applied in the ‘Country’ field for the value of ‘United States’: £27.81 – this is the ad revenue in 2023 from visitors located in the US.
This identifier is useful for showing results for a subset of records that you want the user to be able to narrow down further through filtering.
The ‘all records identifier’: 1
The ‘all records identifier’ is designed to ignore any selections made by users. This means that the set analysis will always return the same value regardless of the current selections in the app. For example, we want to create a KPI that shows ad revenue in 2023 and is NOT responsive to any selections made by the user.
Sum({1<[Date.Calendar.Year] = {2023}>} [Revenue])
In 2023, our blog earned £39.07 through ad revenue. Therefore, the results of this KPI in various scenarios are:
- No selections are made: £39.07 – total ad revenue in 2023.
- A filter is applied in the ‘Country’ field for the value of ‘United States’: £39.07 – the result does not change.
This is useful for showing results that don’t change when users interact with the app.
The ‘previous selection identifier’: $1
The ‘previous selections identifier’ is a little confusing when you first use it. It applies the set expression over records that were returned by the PREVIOUS user selection. For example, we want to create a KPI that shows ad revenue in 2023 and is responsive to the PREVIOUS selection made by the user. The syntax is as follows:
Sum({$1<[Date.Calendar.Year] = {2023}>} [Revenue])
In 2023, our blog earned £39.07 through ad revenue. Therefore, the results of this KPI in various scenarios are:
- No selections are made: £27.81 – the result is based on the selection before it was cleared. The previous selection was the ‘Country’ of ‘United States’. The £27.81 is the ad revenue earned in 2023 from visitors in the United States.
- A filter is applied for the ‘Country’ of ‘United States’: £39.07 – the previous selection was nothing. Hence, the result is the total ad revenue earned in 2023.
Note that you can go back to more than the last selection. Using the identifier $2 will look for records from the selection before last; $3 will look for one before that and so on.
This is useful for showing comparative results. For example, you could have two measures in a table: one looking at current selections and one looking at previous selections. The user filters by one country and then another. The table shows the measure for the currently selected country in one column and the previously selected country in another.
The ‘next selection identifier’: $_1
The ‘next selections identifier’ is the opposite of the ‘previous selections identifier’. It applies the set expression over records that will be returned by the NEXT user selection. Note that if there is no next selection available, the set analysis will return a 0 result. For example, we want to create a KPI that shows ad revenue in 2023 and is responsive to the NEXT selection made by the user. The syntax is as follows:
Sum({$_1<[Date.Calendar.Year] = {2023}>} [Revenue])
In 2023, our blog earned £39.07 through ad revenue. Therefore, the results of this KPI in various scenarios are:
- No selections are made: £0.00 – the result is £0 because the user hasn’t made any selections that have been undone yet.
- A filter is applied for the ‘Country’ of ‘United States’, cleared, and using the selection tool the user undoes the last action: £39.07 – the next selection was nothing. Hence, the result is the total ad revenue earned in 2023.
This is also useful for comparative results or exploration. For example, in an app that is designed for the user to ‘play’ with the selections, this can be a good way of showing them what their next selection showed after they undid the current selection.
The ‘bookmark identifier’: BOOKMARK
The bookmark identifier tells Qlik to narrow the data to records returned by a specific bookmark. You can use the bookmark ID or name to refer to an existing bookmark. For example, we have a bookmark with the country selection of the United States – the bookmark name is ‘Country: US’. We want to create a KPI that shows ad revenue in 2023 and is narrowed down to visitors from the United States by using the bookmark. Here is the syntax:
Sum({'Country: US' <[Date.Calendar.Year] = {2023}>} [Revenue])
Note that the bookmark name has to be surrounded by single quotes because there is a space between some of the characters. Also, note that this type of identifier will not be affected by any selections the user makes.
The result is £27.81 – the ad revenue earned in 2023 from visitors in the United States.
This is particularly useful from a developer’s perspective. You can save a long set of filters as a bookmark and point all relevant measures to the bookmark identifier instead of writing out a long expression with many modifiers in every relevant measure.
The ‘alternate state identifier’: Alt
The alternate state identifier tells Qlik to narrow the data to records returned by selections in a specific alternate state. You have to use the name of the state as an identifier. For example, we created an alternate state called ‘Alt1’. We then created two filter panes for the field ‘Country’. One works with the default state and the other with the new state Alt1. If we want to create a KPI that shows the ad revenue in 2023 for selections made in the state Alt1, this is the syntax:
Sum({Alt1 <[Date.Calendar.Year] = {2023}>} [Revenue])
In 2023, our blog earned £39.07 through ad revenue. Therefore, the results of this KPI in various scenarios are:
- No selections are made: £39.07 – the KPI shows the total ad revenue in 2023.
- Selections are made for the ‘United States’ in the default state: the Alt1 KPI still shows the total ad revenue in 2023.
- Selections are made for the ‘United States’ in the Alt1 state: the KPI shows the ad revenue in 2023 from visitors in the United States.
This is useful for comparative purposes. The user can select different values in the same fields in two states and see the results next to each other. For example, one KPI could point to the default state and a second one to an alternate state. When the user chooses a country in each filter pane, each KPI will show the results for the respective country only.
