A set can be modified by an additional or a changed selection. Such a modification can be written in the set expression.
The modifier consists of one or several field names, each followed by a selection that should be made on the field, all enclosed by < and >. For example: <Year={2007,+2008},Region={US}>. Field names and field values can be quoted as usual, e.g. <[Sales Region]={’West coast’, ’South America’}>.
A set modifier can be used on a set identifier or on its own. It cannot be used on a set expression. When used on a set identifier, the modifier must be written immediately after the set identifier, for examples {$<Year = {2007, 2008}>}. When used on its own, it is interpreted as a modification of the current selection.
There are several ways to define the selection as described in the following.
Based on another field
A simple case is a selection based on the selected values of another field, for example <OrderDate = DeliveryDate>. This modifier will take the selected values from DeliveryDate and apply those as a selection on OrderDate. If there are many distinct values – more than a couple of hundred – then this operation is CPU intense and should be avoided.
Based on element sets (a field value list in the modifier)
The most common case is a selection based on a field value list enclosed in curly brackets, the values separated by commas, for example <Year = {2007, 2008}>. The curly brackets here define an element set, where the elements can be either field values or searches of field values. A search is always defined by the use of double quotes, for example <Ingredient = {"*Garlic*"}> will select all ingredients including the string ’garlic’. Searches are case-insensitive and are made also over excluded values.
Empty element sets, either explicitly for example <Product = {}> or implicitly for example <Product = {"Perpetuum Mobile"}> (a search with no hits) mean no product, i.e. they will result in a set of records that are not associated with any product. Note that this set cannot be achieved through usual selections, unless a selection is made in another field, for example, TransactionID.
Forced exclusion
Finally, for fields in and-mode, there is also the possibility of forced exclusion. If you want to force exclusion of specific field values, you will need to use “~” in front of the field name.
Examples and results:
| Examples | Results |
|---|---|
| sum( {1<Region= {USA} >} Sales ) | Returns the sales for the region USA disregarding the current selection |
| sum( {$<Region = >} Sales ) | Returns the sales for the current selection, but with the selection in 'Region' removed |
| sum( {<Region = >} Sales ) |
Returns the same as the example immediately above. When the set to modify is omitted, $ is assumed. Information noteThe syntax in the two previous examples is interpreted as “no selections” in 'Region', that is to say all regions given other selections will be possible. It is not equivalent to the syntax <Region = {}> (or any other text on the right side of the equal sign implicitly resulting in an empty element set) which is interpreted as no region.
|
| sum( {$<Year = {2000}, Region = {US, SE, DE, UK, FR}>} Sales ) | Returns the sales for current selection, but with new selections both in 'Year' and in 'Region'. |
| sum( {$<~Ingredient = {“*garlic*”}>} Sales ) | Returns the sales for current selection, but with a forced exclusion of all ingredients containing the string 'garlic'. |
| sum( {$<Year = {“2*”}>} Sales ) | Returns the sales for the current selection, but with all years beginning with the digit “2”, i.e. most likely year 2000 and onwards, selected in the field 'Year'. |
| sum( {$<Year = {“2*”,”198*”}>} Sales ) | As above, but now also the 1980:s are included in the selection. |
| sum( {$<Year = {“>1978<2004”}>} Sales ) | As above, but now with a numeric search so that an arbitrary range can be specified. |
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!