Expression Syntax for Derived Fields Formulas

Expression is a formula that is used during Derived Field creation process for calculating new required values. This article contains a table with all supported expressions, their description and examples of their usage.

For general information on Derived fields, refer to Understanding Derived Fields article.

Supported SQL Mathematical Functions

Supported Expression Description Example
abs The absolute value or modulus is used to turn a negative number into positive.
 abs([sales]) ; abs([sales]-[Prior sales])
ceiling (ceil)
 Returns the smallest integer value which is greater than, or equal to the specified number.
 ceiling([sales]) ; ceil([sales]-[Prior sales])
div Division.
 [sales]/2
floor
 Returns the largest integer value which is greater than, or equal to the specified number.
 floor ([sales]) ; floor([sales]-[Prior sales])
exp Used to get the value of the base of natural logarithm number e, raised to the power of a number specified as argument.
if Takes 3 expressions and if the first expression is true, not zero and not NULL, it returns the second expression. Otherwise, it returns the third expression.
 if([sales]>1000,1,0) ; if([sales]-[Prior sales]>1000,1,0)
ifnull Takes 2 expressions and if the first expression is not NULL, it returns the first expression. Otherwise, it returns the second expression
 ifnull([sales],[sales]) ; ifnull([sales],[Prior sales])
log (ln)
 The inverse of the exp() function.
 log([sales]) ; log([sales]-[Prior sales])
mod
 Returns the remainder of a number divided by another number.
pi Returns the value of π(pi)
 pi()*[sales] ; pi()*([sales]-[Prior sales])
power (pow) Returns the value of a number raised to another.
 pow([sales],2) ; pow([sales]-[Prior sales],2)
radians Converts the value of a number from degrees to radians.
 radians([sales]) ; radians([sales]-[Prior sales])
rand Returns a random floating-point value between the range 0 to 1. When a fixed integer value is passed as an argument, the value is treated as a seed value and as a result, a repeatable sequence of column values will be returned.
round Rounds a number to a specified amount of decimal places specified as an argument up to a number specified as another argument.
 round([sales],2) ; round([sales]-[Prior sales],2)
sign Returns the sign of the argument.
 sign([sales]) ; sign([sales]-[Prior sales])
sin Returns the sine of the argument.
 sin([sales]) ; sin([sales]-[Prior sales])
sqrt Return the square root of the argument.
 sqrt([sales]) ; pow([sales]-[Prior sales])