Skip to main content

Set modifiers using set functions

Sometimes you need to define a set of field values using a nested set definition. For example, you may want to select all customers that have bought a specific product, without selecting the product.

In such cases, use the element set functions P() and E(). These return the element sets of possible values and excluded values of a field, respectively. Inside the brackets, you can specify the field in question, and a set expression that defines the scope. For example:

P({1<Year = {2021}>} Customer)

This will return the set of customers that had transactions in 2021. You can then use this in a set modifier. For example:

Sum({<Customer = P({1<Year = {2021}>} Customer)>} Amount)

This set expression will select these customers, but it will not restrict the selection to 2021.

These functions cannot be used in other expressions.

Additionally, only natural sets can be used inside the element set functions. That is, a set of records that can be defined by a simple selection.

For example, the set given by {1-$} cannot always be defined through a selection, and is therefore not a natural set. Using these functions on non-natural sets will return unexpected results.

Examples: Chart expressions for set modifiers using set functions