Skip to main content Skip to complementary 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

Examples Results
sum( {$<Customer = P({1<Product={'Shoe'}>} Customer)>} Sales )

Returns the sales for current selection, but only those customers that ever have bought the product 'Shoe'. The element function P( ) here returns a list of possible customers; those that are implied by the selection 'Shoe' in the field Product.

sum( {$<Customer = P({1<Product={'Shoe'}>})>} Sales )

Same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

sum( {$<Customer = P({1<Product={'Shoe'}>} Supplier)>} Sales )

Returns the sales for current selection, but only those customers that ever have supplied the product 'Shoe', that is, the customer is also a supplier. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection 'Shoe' in the field Product. The list of suppliers is then used as a selection in the field Customer.

sum( {$<Customer = E({1<Product={'Shoe'}>})>} Sales )

Returns the sales for current selection, but only those customers that never bought the product 'Shoe'. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection 'Shoe' in the field Product.