Skip to main content

Set analysis

When you make a selection in an app, you define a subset of records in the data. Aggregation functions, such as Sum(), Max(), Min(), Avg(), and Count() are calculated based on this subset.

In other words, your selection defines the scope of the aggregation; it defines the set of records on which calculations are made.

Set analysis offers a way of defining a scope that is different from the set of records defined by the current selection. This new scope can also be regarded as an alternative selection.

This can be useful if you want to compare the current selection with a particular value, for example last year’s value or the global market share.

Set expressions

Set expressions are used inside aggregation functions, and enclosed in curly brackets. For example:

Sum( {$<Year={2021}>} Sales )

A set expression consists of a combination of the following elements:

  • Identifiers. A set identifier represents a selection, defined elsewhere. It also represents a specific set of records in the data. It could be the current selection, a selection from a bookmark, or a selection from an alternate state. A simple set expression consists of a single identifier, such as the dollar sign, {$}, which means all records in the current selection.

    Examples: $, 1, BookMark1, State2

    See: Set identifiers

  • Operators. A set operator can be used to create unions, differences or intersections between different set identifiers. This way, you can create a subset or a superset of the selections defined by the set identifiers.

    Examples: +, -, *, /

    See: Set operators

  • Modifiers. A set modifier can be added to the set identifier to change its selection. A modifier can also be used on its own and will then modify the default identifier. A modifier must be enclosed in angle brackets <…>.

    Examples: <Year={2020}>, <Supplier={ACME}>

    See: Set modifiers

The elements are combined to form set expressions.

Elements in a set expression

Elements in a set expression.

This set expression is built from the aggregation Sum(Sales).

The first operand returns sales for the year 2021 for the current selection, which is indicated by the $ set identifier and the modifier containing the selection of year 2021. The second operand returns Sales for Sweden, and ignores the current selection, which is indicated by the 1 set identifier.

Finally, the expression returns a set consisting of the records that belongs to any of the two set operands, as indicated by the + set operator.

For a full syntax description, see Syntax for set expressions.

Examples

Examples that combine the set expression elements above are available in the following topics:

To try out some other expressions, see Tutorial - Creating a set expression.

Natural sets

Usually, a set expression represents both a set of records in the data model, and a selection that defines this subset of data. In this case, the set is called a natural set.

Set identifiers, with or without set modifiers, always represent natural sets.

However, a set expression using set operators also represents a subset of the records, but can generally still not be described using a selection of field values. Such an expression is a non-natural set.

For example, the set given by {1-$} cannot always be defined by a selection. It is therefore not a natural set. This can be shown by loading the following data, adding it to a table, and then making selections using filter panes.

Load * Inline
[Dim1, Dim2, Number
A, X, 1
A, Y, 1
B, X, 1
B, Y, 1];		

By making selections for Dim1 and Dim2, you get the view shown in the following table.

Table with natural and non-natural sets

Table with natural and non-natural sets.

The set expression in the first measure uses a natural set: it corresponds to the selection that is made {$}.

The second measure is different. It uses {1-$}. It is not possible to make a selection that corresponds to this set, so it is a non-natural set.

This distinction has a number of consequences:

  • Set modifiers can only be applied to set identifiers. They cannot be applied to an arbitrary set expression. For example, it is not possible to use a set expression such as: 

    { (BM01 * BM02) <Field={x,y}> }

    Here, the normal (round) brackets imply that the intersection between BM01 and BM02 should be evaluated before the set modifier is applied. The reason is that there is no element set that can be modified.

  • You cannot use non-natural sets inside the P() and E() element functions. These functions return an element set, but it is not possible to deduce the element set from a non-natural set.

  • A measure using a non-natural set cannot always be attributed to the right dimensional value if the data model has many tables. For example, in the following chart, some excluded sales numbers are attributed to the correct Country, whereas others have NULL as Country.

    Chart with non-natural set

    Table with non-natural set.

    Whether or not the assignment is made correctly depends on the data model. In this case, the number cannot be assigned if it pertains to a country that is excluded by the selection.