The values that we want to assign to the fields in our set modifiers can use set operators in the different element sets. The following set analysis explains this:
- All sales from 2010 to 2019 but excluding the year 2016:
sum({$< [Year] = {"201*"} - {2016} >} Sales)
As we include a non-numerical character (*) in a value that must be numerical (Year), you must enclose the values in double quotes.
In this example, the wildcard * can be replaced by ?. The star returns any character of any length while the question mark ? returns any character of length 1. As years have four digits, using * or ? would throw the same result.
- Sales for the current selection in the Product Id field but adding product code 3 if this is not yet selected:
sum({$< [Product Id] = [Product Id] + {3} >} Sales)
- Number of products sold in 2018 by all sales managers but Mary:
count({$< [Year] = {2018}, [Sales Manager] = {"*"} - {"Mary"} >} [Product Id])