Using Expressions to Perform Data Calculations

Users can use expressions to perform calculations on queried data for data elements and display elements. Different expressions are available for selection depending on the input data for a calculation.

The following expressions are available for any calculation:

To populate a field with a hardcoded value, users must enter the value and select it from the dropdown menu.

Expression

Description

Hardcoded string

User-defined string

Hardcoded number

User-defined number. Can be a float or an integer.

Hardcoded date

User-defined date as a string

Hardcoded bool

User-defined true or false value

Field from singleton

Copies a value from any data element with a single record output

Count Unique

Returns the number of unique values in a given list of values

Sum

Returns the sum of a given list of numbers

Product

Returns the product of a given list of numbers

Addition

Returns the sum of two numbers

Subtraction

Returns the difference of two numbers

Multiplication

Returns the product of two numbers

Division

Returns quotient of two numbers

Round

Returns a number rounded to the given number of decimal places

Format Date

Returns a date string in the given date format. Available date components include:

  • y - year
  • Y - week-numbering year
  • Q - quarter
  • M - month. Must be uppercase.

    M is not strictly numeric. Use MMM to get the three-letter abbreviated month name and MMMM to get the full month name. Month names are not translated.

  • w - week number
  • d - day. Must be lowercase.

    Use ddd to get ordinal numbers, for example, 1st or 23rd.

  • D - ordinal day
  • E - weekday name
  • e - weekday number

These date components can be used in any order with any normal separator, including slashes, dashes, spaces, periods, and commas.

Year

Returns the year number from a given date

Month

Returns the month number from a given date

Day in Month

Returns the month day number from a given date

Join Multiple Texts

Joins a given list of strings into a single string

Pick a value arbitrarily

Returns a single value from a given list of values

Max

Returns the maximum value from a given list of values

Min

Returns the minimum value from a given list of values

Floor of Date

Calculates the nearest past date from a given date and a selected date interval

Ceiling of Date

Calculates the nearest future date from a given date and a selected date interval

Addition (Date)

Calculates a date from a given date plus a selected date interval

Subtraction (Date)

Calculates a date from a given date minus a selected date interval

Not

Returns the opposite true or false value of the given value

Equals

Checks if two values are equal. Returns true or false.

Not Equals

Checks if two values are note equal. Returns true or false.

Less Than

Checks if a value is less than a second value. Returns true or false.

Less Than or Equals

Checks if a value is less than or equal to a second value. Returns true or false.

Greater Than

Checks if a value is greater than a second value. Returns true or false.

Greater Than or Equals

Checks if a value is greater than or equal to a second value. Returns true or false.

In

Checks if a list of strings contains the given string. Returns true or false.

Not In

Checks if a list of strings does not contain the given string. Returns true or false.

All

Checks if all values in a list of values are true

Any

Checks if any value in a list of values if true

Number to Text

Converts a number value to a string

If

Defines a condition evaluating to true or false and the expected output for each result

Field values from a list of records

Returns a list of values for a selected field from an existing data element

List of individual values

Returns a list of values of the same data type

The following expressions are only available when performing calculations on a selected list of records:

Expression

Description

Local Field

Returns a value from a field in a given data element

Related Field

Returns a list of values satisfying a given condition derived from a list of field values in the selected data element