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