Calculated field syntax

Calculated field syntax guidelines

Follow the following syntax guidelines when you create calculated fields in Ad Hoc views.
  • To reference a text string, use single quotation marks ('). For example, 'Text String'.
  • To reference a field label, use double quotation marks ("). For example, "Ad Hoc Label".
  • To reference date constants, indicate the date type as part of the syntax:
    • To reference a date without time data, use d followed by single quotation marks ('). Use the following format: yyyy-dd-mm. For example, d'2014-06-10'.
    • To reference a date with day and time data, use ts followed by single quotation marks ('). If you use ts and enter the date information only, the time is automatically set to 00:00:00. Use the following format: yyyy-dd-mm hh:mm:ss. For example, ts'2014-06-10 01:30:00'.
    • To reference a date with time data only, use t followed by single quotation marks ('). Use the following format: hh:mm:ss). For example, t'01:30:00'\.
    • To reference a date field label, use double quotation marks ("). For example, "Ad Hoc Date Field Label".
Note: The following are reserved words and cannot be used as field names regardless of the case unless they are part of a phrase such as Not Available:
  • And
  • In
  • Not
  • Or
When dates are used in comparisons or the IF function, they both need to be one of the following types:
  • Date only
  • Date and time
  • Time only
Make sure to use the correct modifier when you use date constants in comparisons:
  • d
  • ts
  • t
The argument name in the function descriptions for calculated fields describes the type of input the function accepts as described in Calculated fields reference:
BooleanExpression
Any expression that takes on Boolean values, including the label of a Boolean field or measure, a Boolean calculation, or a Boolean value.
Note: You can create a BooleanExpression by using the following elements:
  • Comparison operators.
    • ==
    • !=
    • >
    • >=
    • <
    • <=
    • in
  • Functions that return Boolean values.
    • StartsWith
    • EndsWith
    • IsNull
    • Contains
  • Logical functions.
    • AND
    • OR
    • NOT
DateExpression
Any type of date or time stamp values, including the label of a date field or measure, or a calculation that returns dates.
DateTimeExpression
Date expressions that contain time values, including the label of a date field or measure, or a calculation that returns dates. These values are also known as time stamp values.
Expression
Any valid date, date and time, numeric, or string expression.
NumericExpression
Numeric values, including the label of a numeric field or measure, or a calculation that returns numbers.
TextExpression
Text values, including the label of a text field or measure, or a text string.
Level
For aggregate functions, specifies the set of values that are used to compute the calculation. Possible values for Level:
  • Current (not available for PercentOf)
  • ColumnGroup
  • ColumnTotal
  • RowGroup
  • RowTotal
  • Total
For more information, see Aggregate functions.