Formula Reference for Custom Attributes and Measures

For some data models you need custom attributes or custom measures. See Creating Custom Attributes and Creating Custom Measures.

You create custom attributes and custom measures using formulas that are calculated on the data tier. See Introduction to BQL - Queries and Where Calculations Occur.

Important: The syntax for custom formulas is similar to BQL but not the same.

The formulas use the following functions and grammar.

Attribute and Measure Names
Conditional Logic
Functions for Custom Formulas
Grammar for Custom Formulas

Attribute and Measure Names in the Formula Field

Unlike BQL, use the "simple" name of the attribute or measure in a formula.

The Custom Measure and Custom Attribute dialogs add the dimension and aggregation syntax behind the scenes.

You can also see an attribute's dimension in the Custom Attributes list.

You can see a measure's grain in the Custom Measures tab.

Conditional Logic

Custom formulas may include conditional logic. For example, to define a new measure that returns 1 when Quantity is greater than 20 and otherwise return 0:

IF ([Quantity]>20) THEN 1 ELSE 0 END

Supported Functions

The following functions are supported by custom formulas for use in custom attributes and measures.

Function for Custom Formula	Description
DATEDIFF(YEAR\|QUARTER\|MONTH\|WEEK \|DAY\|HOUR\|MINUTE\|SECOND, start_date_attribute,end_date_attribute\|NOW)	Returns the difference between two dates as an integer value of the specified type. The dates must be attributes, not measures. The column type must be DATE or DATETIME. NULLs are interpreted as zero (0). To get the difference between a date and the current date and time, use NOW as the second date argument, for example: DATEDIFF(DAY,[Order_Date],NOW)
DATEPART(YEAR\|QUARTER\| MONTH\|WEEK\|DAY,date_attribute)	Returns a specific date part of a date attribute as a string. The date must be an attribute, not a measure. For example: DATEPART(DAY,[Order_Date])
SUBSTRING(attribute, start_position_number, end_position number)	Returns a portion of a given string. Uses a zero based index. First position is 0, ending position is the position after the last one that should be included.
LENGTH(string)	Returns the number of characters in a string.
POSITION(substring,string)	Returns the location of a substring in a string.
Sin(logical column)	Returns the sine of a given angle.
Cos(logical column)	Returns the cosine of a given angle.
Tan(logical column)	Returns the tangent of a given angle.
ArcSin(logical column)	Returns the arc sine of a number between -π/2 and π/2.
ArcCos(logical column)	Returns the arc cosine of a number between ο and π.
ArcTan(logical column)	Returns the arc tangent of a number between -π/2 and π/2.
abs(measure)	Absolute value of a measure value.
ceiling(measure)	Round up. (Returns the closest integer greater than or equal to the given number.)
Floor(logical column)	Returns the closest integer less than or equal to a given number.
sqrt(measure)	Returns the square root of a given positive number.
Ln(logical column)	Returns the natural logarithm of a given number in a specified base.
Log(logical column)	Returns the logarithm of a given number in a specified base.
Exp(logical column)	Returns e raised to the power of the number given.
POW(x,y)	Raise x to the power of y.

Formula Grammar

The following is the grammar for formulas for custom attributes and custom measures.

expression

logicalExpression EOF!;

logicalExpression

booleanAndExpression (OR^ booleanAndExpression )* ;

OR

'||' | 'or' | 'OR';

booleanAndExpression

equalityExpression (AND^ equalityExpression)* ;

AND

'&&' | 'and' | 'AND';

Bitwise OR

'|';

Bitwise AND

'&';

equalityExpression

relationalExpression ((EQUALS|NOTEQUALS)^ relationalExpression)*;

EQUALS

'=' | '==';

NOTEQUALS

'!=' | '<>';

relationalExpression

additiveExpression ( (LT|LTEQ|GT|GTEQ)^ additiveExpression )*;

LT

'<';

LTEQ

'<=';

GT

'>';

GTEQ

'>=';

additiveExpression

multiplicativeExpression ( (PLUS|MINUS)^ multiplicativeExpression )*;

PLUS

'+';

MINUS

'-';

multiplicativeExpression

powerExpression ( (MULT|DIV|MOD)^ powerExpression )*;

MULT

'*';

DIV

'/';

MOD

'%';

powerExpression

unaryExpression ( POW^ unaryExpression )*;

POW

'^';

unaryExpression

primaryExpression
|              NOT^ primaryExpression
|              MINUS primaryExpression -> ^(NEGATE primaryExpression);

NOT

'!' | 'not';

primaryExpression

'(' logicalExpression ')' -> ^(PAREN logicalExpression)
|              value ;

value

INTEGER
|              FLOAT
|              DATETIME
|              BOOLEAN
|              STRING
|              function
|              conditional
|              LOGICALCOLUMN;

STRING

'\ ( EscapeSequence | (options {greedy=false;} : ~('\u0000'..'\u001f' | '\\' | '\ ) ) )* '\;

INTEGER

('0'..'9')+;

FLOAT

('0'..'9')* '.' ('0'..'9')+;

DATETIME

'#' (~'#')* '#' | 'NOW' | 'now';

BOOLEAN

'TRUE'
'FALSE';

function

datediff | substring | length | position;

datediff

DATEDIFF^ '('! (YEAR|MONTH|DAY) ','! logicalExpression ','! logicalExpression ')'!;

DATEDIFF

'DATEDIFF' | 'datediff';

YEAR

'YEAR'|'year';

MONTH

'MONTH'|'month';

DAY

'DAY'|'day';

substring

SUBSTR^ '('! logicalExpression ','! logicalExpression (','! logicalExpression)? ')'!;

SUBSTR

'SUBSTRING' | 'substring';

length

LENGTH^ '('! logicalExpression ')'!;

LENGTH

'LENGTH' | 'length';

position

POSITION^ '('! logicalExpression ','! logicalExpression ')'!;

POSITION

'POSITION' | 'position';

LOGICALCOLUMN

'[' IDENT ']';

fragment IDENT

('a'..'z' | 'A'..'Z' | '_') ('a'..'z' | 'A'..'Z' | '_' | ' ' | ':' | '$' | '#' | '%' | '0'..'9')*;

conditional

condition+ ('ELSE' logicalExpression)? 'END' -> ^(CONDITIONAL condition* logicalExpression);

condition

'IF' logicalExpression 'THEN' logicalExpression -> ^(CONDITION logicalExpression logicalExpression);

fragment EscapeSequence

'\\'
(              
'n' 
|              'r' 
|              't'
|              '\   
|              '\\'
|              UnicodeEscape
);

fragment UnicodeEscape

'u' HexDigit HexDigit HexDigit HexDigit ;

fragment HexDigit

('0'..'9'|'a'..'f'|'A'..'F') ;

/* Ignore white spaces */ WS

(' '|'\r'|'\t'|'\u000C'|'\n') {$channel=HIDDEN;};