Expression Syntax for Derived Fields Formulas

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])

0 Comments

Add your comment

E-Mail me when someone replies to this comment