Set modifiers with searches
You can create element sets through searches with set modifiers.
For example:
- <Country = {"C*"}>
- <Year = {">2015"}>
- <Ingredient = {"*garlic*"}>
Searches should always be enclosed in double quotes, square brackets or grave accents. You can use a list with a mixture of literal strings (single quotes) and searches (double quotes). For example:
<Product = {'Nut', "*Bolt", Washer}>
Text searches
Wildcards and other symbols can be used in text searches:
-
An asterisk (*) will represent any number of characters.
-
A question mark (?) will represent a single character.
-
A circumflex accent (^) will mark the beginning of a word.
For example:
-
<Country = {"C*", "*land"}>
Match all countries beginning with a C or ending with land.
-
<Country = {"*^z*"}>
This will match all countries that have a word beginning with z, such as New Zealand.
Numeric searches
You can make numeric searches using these relational operators: >, >=, <, <=
A numeric search always begins with one of these operators. For example:
-
<Year = {">2015"}>
Match 2016 and subsequent years.
-
<Date = {">=1/1/2015<1/1/2016"}>
Match all dates during 2015. Note the syntax for describing a time range between two dates. The date format needs to match the date format of the field in question.
Expression searches
You can use expression searches to make more advanced searches. An aggregation is then evaluated for each field value in the search field. All values for which the search expression returns true are selected.
An expression search always begins with an equals sign: =
For example:
<Customer = {"=Sum(Sales)>1000"}>
This will return all customers with a sales value greater than 1000. Sum(Sales) is calculated on the current selection. This means that if you have a selection in another field, such as the Product field, you will get the customers that fulfilled the sales condition for the selected products only.
If you want the condition to be independent of the selection, you need to use set analysis inside the search string. For example:
<Customer = {"=Sum({1} Sales)>1000"}>
The expressions after the equals sign will be interpreted as a boolean value. This means that if it evaluates to something else, any non-zero number will be interpreted as true, while zero and strings are interpreted as false.
Quotes
Use quotation marks when the search strings contain blanks or special characters. Single quotes imply a literal, case-sensitive match with a single field value. Double quotes imply a case insensitive search that potentially matches multiple field values.
For example:
-
<Country = {'New Zealand'}>
Match New Zealand only.
-
<Country = {"New Zealand"}>
Match New Zealand, NEW ZEALAND, and new zealand
Double quotes can be substituted by square brackets or by grave accents.
For more information, see Searching within selections or visualizations.
Examples: Chart expressions for set modifiers with searches
Examples | Results |
---|---|
sum( {$–1<Product = {“*Internal*”, “*Domestic*”}>} Sales ) |
Returns the sales for current selection, excluding transactions pertaining to products with the string 'Internal' or 'Domestic' in the product name. |
sum( {$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales ) |
Returns the sales for current selection, but with a new selection in the 'Customer' field: only customers who during 2007 had a total sales of more than 1000000. |