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 options.
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