Both filters and computed fields use expressions enabling you to compute and manipulate data. In the Filter example, OriginalQuote <> 0 was the expression; in the Computed Fields example, (cmpToRevenueAmount / cmpToExpenseAmount) – 1 was the expression.
Expressions can do comparisons, computations, or aggregations. They can include both operators and functions. Text springs require single quote marks around them, and Grouping with parentheses is recommended.
Operators
| Operator | Explanation | Example | Possible Result |
| < | Less Than | OriginalQuote < 5 | 4, 3, 2, etc. |
| > | Greater Than | OriginalQuote > 5 | 6, 7, 8, etc. |
| <= | Less Than or Equal | OriginalQuote <= 5 | 5, 4, 3, etc. |
| >= | Greater Than or Equal | OriginalQuote >= 5 | 5, 6, 7, etc. |
| <> | Not Equal | OriginalQuote <> 0 | -1, 1, 2, etc. |
| = | Equal | OriginalQuote = 5 | 5 |
| IN | In the (list) | State IN (‘NY’,’NJ’,’CT) | NY, NJ, CT |
| LIKE | Like the text and wildcard that follows. Wildcards are * or % and they can be used before or after the text string. | State LIKE N% | NE, NM, NY, etc. |
| + | Concatenation | City + ‘, ’ + State | Norwalk, CT |
| E.g., OriginalQuote = 1000 in the following: | |||
| + | Plus | OriginalQuote + 10 | 1010 |
| – | Minus | OriginalQuote – 10 | 990 |
| * | Multiplied By | OriginalQuote * 100 | 100,000 |
| / | Divided By | OriginalQuote / 10 | 100 |
| % | Remainder after division | OriginalQuote % 3 | 1 |
Functions
| Function | Explanation | Example | Possible Result |
| IIF | IIF(test, a,b) If test is true, returns a, otherwise returns b |
IIF(Quantity > 50, ‘Yes’, ‘No’) | 51 = Yes 49 = No |
| ISNULL | ISNULL(a,b) If a is null (i.e., not specified), returns b, otherwise returns a |
ISNULL(Quantity, ‘n/a’) | = n/a 20 = 20 |
| LEN | The string length | LEN(State) | 2 |
| TRIM | Removes leading and trailing blanks, tabs, carriage returns and line feeds | TRIM(City) | |
| SUBSTRING | SUBSTRING(String,start,len) Extracts part of a string starting at the specified character and for the length specified |
SUBSTRING(City,1,4) | Philadelphia = Phil |