Calculated Field Syntax

This section describes the syntax used when creating calculated fields in Ad Hoc views.

Use the following syntax for inputs:

• Text String: to reference a text string, use single quotes: (')]— 'Text String'.

• Field Label: to reference a field label, use double quotes: (") — "Ad Hoc Label".

• Date Constants: to reference date constants, indicate the date type as part of the syntax, as listed below:

  • To reference a date without time data (for example: yyyy-dd-mm), use "d" followed by single quotes: (').

  • To reference a date with day and time data (for example: yyyy-dd-mm hh:mm:ss), use "ts" followed by single quotes: (') — "ts'2014-06-10 01:30:00'". If you use "ts" and enter the date information only, the time is automatically set to "00:00:00."

  • To reference a date with time data only (hh:mm:ss), use "t" followed by single quotes: (').

  • To reference a date field label, use double quotes: (") — "Ad Hoc Date Field Label".
    
    

    TipThe following are reserved words and cannot be used as field names: AND, And, and, IN, In, in, NOT, Not, not, OR, Or, or.
    Names containing these strings, such as "Not Available", can be used.
    
    When dates are used in comparisons or the IF function, they must be the same type, (date only, date/time, or time only). Make sure to use the correct modifier (d, ts, t) when using date constants in comparisons.

• • BooleanExpression: any expression that takes on Boolean values, including the label of a Boolean field or measure, a Boolean calculation, or a Boolean value. You can create a BooleanExpression using the following: comparison operators: "(==, !=, >, >=, <, <=, in)." Functions that return Boolean values: "StartsWith," "EndsWith," "IsNull," "Contains," and logical functions: "AND," "OR," "NOT."

  • DateExpression: any type of date or timestamp 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 timestamp values.

  • Expression: any valid date, date-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 used to compute the calculation. Possible values include: "Current" (not available for "PercentOf"), "ColumnGroup," "ColumnTotal," "RowGroup," "RowTotal," and "Total."