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
Plus	Returns the sum of two numbers
Minus	Returns the difference of two numbers
Times	Returns the product of two numbers
Divide By	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.
Format Date/Time	Returns a datetime string in the format of the user’s locale
Date/Time to Date	Returns the date from the given datetime value
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
Beginning of Calendar Period	Calculates the date that is at the beginning of the week/month/quarter/year from a given date
End of Calendar Period	Calculates the date that is at the end of the week/month/quarter/year from a given date
Add to Date	Calculates a date from a given date plus a selected interval
Subtract from Date	Calculates a date from a given date minus a selected 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.
Is Null	Checks if a value is null. Returns true or false.
Is Not Null	Checks if a value is not null. 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