Expression Syntax for Derived Fields Formulas
 For general information on Derived fields, refer to: Understanding Derived Fields
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 floatingpoint 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