Using expressions

Analytics expressions are combinations of values and operators that perform a calculation and return a result.

Expressions are a valuable and flexible tool. You can use them to:

perform a wide range of calculations
create filters
prepare data for analysis
create computed fields

The content of expressions

Expressions can include data fields, functions, literals, constants, and variables, which can be combined using arithmetic or logical operators.

You can enter expressions manually, or you can build them using the Expression Builder, which provides a standard utility throughout Analytics for creating expressions.

The complexity of expressions

Expressions can be as simple or as complex as needed.

A simple expression could return the result of a basic arithmetic operation.

For example:

Quantity * Cost

A more complex expression could reference a number of fields and functions and use operators to combine the parts of the expression.

For example:

PROPER(first_name) + " " + PROPER(last_name)

converts all the names in the first_name and last_name fields to proper case (initial capital letter followed by lowercase), and joins the first and last names with a single space between them.

Types of expressions

Analytics supports four types of expressions, which correspond with the four supported data categories, or data types:

character
numeric
datetime
logical

For example:

Amount + 1 is a numeric expression because it performs an operation on numbers and returns a numeric result.
Amount > 1 is a logical expression because it makes a comparison and returns a logical result of True or False.

The content of any expression you create must correspond with the expression type:

Expression type	Required content	Example
Character	Contains any of the following: character fields variables that contain character data functions that return character values quoted character strings (character literals)	Extract the digits from a product code and discard the three-character prefix: SUBSTR(Product_code, 4, 10)
Numeric	Contains any of the following: numeric fields variables that contain numeric data functions that return numeric values literal numeric values, without quotation marks – limited to digits, a minus sign if needed, and a decimal point if needed	Calculate sale price plus tax: Sale_price * 1.07 Find the maximum value across three fields: MAXIMUM(Min_Qty, Qty_on_hand, Qty_on_order)
Datetime	Contains any of the following: datetime fields variables that contain datetime data functions that return datetime values quoted datetime values (datetime literals) The Datetime data type encompasses three subtypes: date, datetime, and time. Quoted datetime values require backquotes – for example, `20141231` or `20141231.235959`. The backquote (`) is the lowercase key at the upper left corner of the keyboard.	Calculate the elapsed days between the two dates: `20141231` - `20141130` Calculate the elapsed time between values in two time fields: Finish_Time - Start_Time
Logical	Contains any of the following: an operation that produces a logical result of True or False (T or F) functions that return logical values If T or F are part of the expression, they must be entered without quotation marks. Note A logical expression can reference fields, variables, or literals of any data type.	Find all records with a payment date past the due date: Payment_date > Due_date Filter records in a table on three cities: MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")

How Analytics evaluates expressions

Analytics evaluates expressions according to the following rules:

Operator precedence	Arithmetic and logical precedence dictates the order in which operators are evaluated. See Operators in Analytics expressions. Use parentheses ( ) to modify the order in which the operators are evaluated.
Operand data type	Each operator works only if its operands are of a compatible data type.
Function parentheses	All Analytics functions require parentheses. Everything inside a function's parentheses is evaluated first, before any other parts of an expression outside the function's parentheses.
Comparing character strings	By default, when character strings of different lengths are compared, the shorter of the two lengths is used. If the Exact Character Comparisons option is selected in the Tables tab in the Options dialog box, the longer of the two lengths is used. For more information, see Table tab (Options dialog box).
Decimal precision	If numbers with different decimal precision are mixed in a numeric expression, the result retains the number of decimal places of the operand with the largest number of decimal places. (This default behavior can be changed with the SET MATH command.) For example: 4 + 5.0 = 9.0 6 * 2.000000 = 12.000000 1.1 * 1.1 = 1.2 1.1 * 1.10 = 1.21 For more information, see Controlling rounding and decimal precision in numeric expressions.

Operators in Analytics expressions

The table below lists the operators that are available for use when you create an expression.

The operators are listed in decreasing order of precedence. When operators of equal precedence appear in an expression, they are evaluated from left to right – unless you use parentheses to specify a particular order of evaluation.


Operators in order of precedence	Description
( )	Parentheses – modify operator precedence, or enclose function parameters
NOT -	Logical NOT Unary minus – minus sign, indicates a negative number
^	Exponentiation – raises a number to a power
* / Operators have equal precedence and are evaluated from left to right	Multiply Divide
+ - Operators have equal precedence and are evaluated from left to right	Add Subtract
+	Concatenate character strings
> < = >= <= <> Operators have equal precedence and are evaluated from left to right Note Operators with two symbols must not contain a space. For example, type >= not > = .	Greater than Less than Equal to Greater than or equal to Less than or equal to Not equal to
AND (or &)	Logical AND
OR (or \|)	Logical OR

[ Back to top ]

Analytics 14.1 Help