Supported Functions for Calculated Columns
Use predefined functions to build up a calculated dimension while preparing data. All the functions listed below appear under Formula Functions in the Create Calculated Column dialog.
String Functions
Function | Syntax | Description | Example |
---|---|---|---|
Trim | TRIM(string) | Returns a copy of a specified string, with leading and trailing spaces removed. | TRIM(" cloud ") = "cloud" |
Contain | CONTAIN(whereStr, whatStr) | Indicates whether a specified string whatStr
is inside another specified string whereStr.
The expression is not case-sensitive.
|
CONTAIN("Cats are grey","aRe") |
Substring | SUBSTRING(string, startIndex, length) | Returns the characters of a specified position within a
specified string.
|
SUBSTRING("Monday", 3) = "day" SUBSTRING("London", 1, 3) = "ond" |
Length | LENGTH( string ) | Returns the number of characters in a specified string.
|
LENGTH('') = 0 LENGTH('How Long') = 8 |
Concat | CONCAT( string, string ) | Concatenates one specified string with another specified string to form a single string. | CONCAT( 'ABC', 'DE' ) = 'ABCDE' |
Replace | REPLACE(string, target, replacement) | For a specified string, returns a string with all occurrences
of a specified target string replaced with
another specified string replacement.
|
REPLACE("Mexico DF", "DF" , "City") returns "Mexico City" |
LowerCase | LOWERCASE(text) | Returns lowercase of a text value. | LOWERCASE('AbC') = 'abc' |
UpperCase | UPPERCASE(text) | Returns uppercase of a text value. | UPPERCASE('AbC') = 'ABC' |
Conversion Functions
Function | Syntax | Description | Example |
---|---|---|---|
MakeDate | MAKEDATE( year, month, day ) | Returns the date of the specified year, month, and day in a
yyyy-mm-dd format.
|
MAKEDATE( 1995, 2, 28 ) = '1995-02-28' |
ToDate | TODATE ( text, format ) |
Converts a specified input string in the specified format to a Date object. The format includes: yy or YY or yyyy or YYYY = years (0-99 or 0001-9999)mm or MM = months (1-12) dd or DD = days (1-31).
|
TODATE('1994.06+02', 'YYYY.MM+DD') = '1994-06-02' |
Date Functions
Function | Syntax | Description | Example |
---|---|---|---|
DateDiff | DATEDIFF(date1, date2, unit) | Calculates the time difference between date1 and date2. Expresses
the difference in a given unit.
|
DATEDIFF( [Date1], [Date 2], "day" ) Returns the number of days between Date1 and Date2 by calculating the difference: Date1 - Date2 = No. of days. |
DateAdd | DATEADD( date, interval, unit ) | Returns the result of adding a time interval specified in certain
granularity to a date value. Use negative values for subtraction.
|
DATEADD([Date], 3 ,"Day") Returns a date where 3 days has been added to [Date]. If [Date] is '2015-04-07', the result is '2015-04-10'. |
DayOfWeek | DayOfWeek(date) | For a specified date, returns the day of the week as a number (1 for Monday to 7 for Sunday). | DayOfWeek( #2012-03-23# ) returns
5. #2012-03-23# is a date constant |
Logical Functions
Function | Syntax | Description | Example |
---|---|---|---|
IF | IF( Condition , <value if true> , <value if false>) | Checks the Condition and if it evaluates to true, then it calculates the value if true. Otherwise it calculates the value if false. | IF([Sales]>100, [Price]*0.8,[Price]*0.6
) If Sales is greater than 100 then discount Price by 20%. Otherwise discount Price by 40%. |
AND | and | Takes two Booleans. Returns true if both values are true; otherwise false. |
false and true returns
false 1 = 1 and 2 = 2 returns true |
OR | or | Takes two Booleans. Returns true if one or both values are true; otherwise false. |
false or true returns
true 1 = 1 or 2 = 3 returns true 1 = 2 or 2 = 3 returns false |
NOT | NOT | Returns the negative of a specified Boolean expression | NOT(false) |
< , <=, >, >= , =, != |
num1 < num2 string1 < string2 |
Returns the comparison results between two operands with compatible types. | 1 = 1 returns true 1 = 2 returns false 1 > 1 returns false2 > 1 returns true |
ISNULL | ISNULL() |
Checks for a Null value. Returns true if a field contains a Null value. Adding NOT to the function reverses the results. |
IF(ISNULL(sale), T, F) returns T when the sale value is blank ( or null), and F when there is a sale value. IF(NOT(ISNULL(sale)), T, F) returns T when there is a sale value, and F when the sale value is blank. |
Mathematical Functions
Function | Syntax | Description | Example |
---|---|---|---|
+, -, /, * | num1 + num2 ...; num1 - num2 ; ..... | These are basic numeric calculations. | |
MIN | MIN(number1, number2, ...) | Returns the minimum value between two or more numbers. | MIN(2014, 2016) = 2014 |
MAX | MAX(num1, num2...) | Returns the maximum value between two or more numbers. | MAX(2014, 2016) = 2016 |
ROUND | ROUND( num, nDecimalPlace ) | Returns the value rounded to a specified number of decimal places. | ROUND(9.1218,1) = 9.1 |
ABS | ABS( num ) | Returns the absolute value of a number. | ABS( -14 ) = 14 |
LOG | LOG(num) | Returns the natural logarithm of a number. | LOG( 100 ) = 4.605 |
LOG10 | LOG10(num) | Returns the base 10 logarithm of a number. | LOG10 ( 100 ) = 2 |
POWER | POWER( number, exponent ) | Returns the result of an number raised to a specific exponent. | POWER( 2, 3 ) = 8 |
SQRT | SQRT( number ) | Returns the square root of a number. | SQRT( 81 ) = 9 |
MOD | MOD( number, deviser ) | Returns the remainder of dividing a specified number <number1> by another specified number <number2>. | MOD(15,2) = 1 |
EXP | EXP( number ) | Natural exponential function. Returns the value of e (2.718) raised to a power. | EXP(2) = 7.3890561 |
CEIL | CEIL(number1, number2) | Returns a real number that is greater than or equal to the
entered number.
|
CEIL(14.2) = 15 CEIL(14.22, 1) = 14.3 |
FLOOR | FLOOR(number1, number2) | Returns a real number that is smaller than the entered 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 |