Functions for Calculated Measures

This is a list of functions you can use in expressions for calculated measures.

Numeric Functions

Function	Description	Syntax	Example
ABS(number)	Returns the absolute value of a number (the number without its sign).	ABS( <num>)	`ABS(-11)` returns 11
EXP(number)	Natural exponential function. Returns the value of e (2.718) raised to a power.	EXP( <number>)	`EXP(3)` returns 20.0855
Floor	Returns the largest integer that is not greater than the specified numeric argument.	FLOOR( <number>)	`FLOOR(14.8)` returns the value 14 `FLOOR(14.82,0)` returns the value 14 `FLOOR(14.82,1)` returns the value 14.8 `FLOOR(14.82,-1)` returns the value 10
LN (LOG)	Returns the natural logarithm of a number.	LN( <number>)	`LOG(100)` returns 4.605
LOG (LOG10)	Returns the natural logarithm of a number specified by <number> and a base specified by <base>, where <base> must be a positive value greater than 1, and <number> must be any positive value.	LOG( <base>, <number>)	LOG (10, 2) returns the natural logarithm for 2 base 10, which is `0.30102999566398114`
MOD (%)	Returns the remainder of a number <number> divided by a divisor <divisor>. When <number> is negative, this function acts differently to the standard computational modulo operation. The following list shows examples of what the MOD function returns as the result: If <divisor> is zero, then <number> is returned. If <number> is greater than 0 and <number> is less than <divisor>, then <number> is returned. If <number> is less than 0 and <number> is greater than <divisor>, then <number> is returned. In cases other than those mentioned above, the remainder of the absolute value of <number> divided by the absolute value of <divisor> is used to calculate the remainder. If <number> is less than 0, then the returned remainder from MOD is a negative number, and if <number> is greater than 0, then the returned remainder from MOD is a positive number.	MOD( <number>, <divisor>)	`MOD(15,2)` returns the value 1 `MOD (15, 4)` returns the value `3` `MOD (-15, 4)` returns the value `-3`
POWER (**)	Calculates a specified base number raised to the power of a specified exponent.	POWER( <base>, <exponent>)	`POWER(2,10)` returns the value `1024`
SQRT	Returns the square root of a number.	SQRT( <number>)	`SQRT(4)` returns 2
GrandTotal	Returns the grand total of all the values in the result set of the measure argument <measure>.	GrandTotal(<measure>)	`GrandTotal(Sales)` returns the aggregated value of Sales.
PercentOfGrandTotal	Returns the percentage of Grand Total of all the values in the result set of the measure argument <measure>.	PercentOfGrandTotal(<measure>)	`%GrandTotal(Sales)` returns the percentage of the grand total that each value represents.

Miscellaneous Functions

Function	Description	Example
GREATEST (MAX)	Returns the largest of two or more numbers.	`MAX(10,20,15)` returns the value 20
LEAST (MIN)	Returns the smallest of two or more numbers.	`MIN(10,20,15)` returns the value 10

Date Type Conversion

Function	Description	Example
TO_DATE()	Converts a date string into the DATE format.	`TO_DATE('2010-01-12', 'YYYY-MM-DD')` returns 2010-01-12 or another format such as Jan 12, 2010. The format depends on your date display settings.
TO_DECIMAL (DECFLOAT)	Converts a value to a decimal data type.	`DECFLOAT("14.6")` returns 14.6
TO_DOUBLE (DOUBLE)	Converts a value to a DOUBLE data type.
TO_INT (INT)	Converts a value to an integer data type.	`INT(9.5)` returns the value 9
TO_INTEGER (INT)	Converts the ARG <value>-ARG to an INTEGER data type.
TO_REAL (FLOAT)	Converts a ARG <value>-ARG to a REAL data type.
TO_TIME()	Converts a time string into a time format.	`ToTime(Sep 18, 2020 5:51:06 AM) returns 5:51:06 AM)` returns 5:51:06 AM
TO_TIMESTAMP	Converts a date string to a TIMESTAMP data type.
TO_VARCHAR	Converts a given value to a VARCHAR data type, with an option to format the output value.
CAST	Returns the value of an expression converted to a supplied data type.

Date Time Functions

Function	Description	Example
DAYS_BETWEEN	Computes the number of entire (24 hour) days between two dates.
MONTHS_BETWEEN	Computes the number of seconds between two specified dates.
SECONDS_BETWEEN	Computes the number of seconds between two specified dates.
YEARS_BETWEEN	Computes the number of years between two specified dates.