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 can be used inside and outside aggregation functions, and are enclosed in curly brackets.

Example: Inner set expression

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

Example: Outer set expression

{<Year={2021}>} Sum(Sales) / Count(distinct Customer)

For more information about inner and outer set expressions, see Inner and outer set expressions.

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.

The set expression above, for example, 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:

• Set modifiers with searches

• Set modifiers with dollar-sign expansions

• Set modifiers with set operators

• Set modifiers with implicit set operators

• Set modifiers using set functions

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.

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.

  

  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.