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 angled brackets: < >. For example: <Year={2007,2008},Region={US}>. Field names and field values can be quoted as usual, for example: <[Sales Region]={’West coast’, ’South America’}>.
A set modifier modifies the selection of the preceding set identifier. If no set identifier is referenced, the current selection state is implicit.
There are several ways to define the selection:
- Based on another field
- Based on element sets (a field value list in the modifier)
- Forced exclusion
These methods are described in the following subsections.
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 intensive 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.
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 ) |
The field Ingredient is in AND mode. 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 ) | Returns the sales for the current selections, but with a numeric search used to scope the range of years to sum the sales across. |