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

The following functions let you work with text values.
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.
  • whereStr: string in which a search is conducted.
  • whatStr: substring that is the object of a search.
 CONTAIN("Cats are grey","aRe")
Substring SUBSTRING(string, startIndex, length) Returns the characters of a specified position within a specified string.
  • string : the string from which a substring is computed.
  • startIndex: position where to start the extraction. Has to be a non-negative number.
  • length: The number of characters to extract. If omitted, it extracts the rest of the string. This is optional.

SUBSTRING("Monday", 3) = "day"

SUBSTRING("London", 1, 3) = "ond"
Length LENGTH( string ) Returns the number of characters in a specified string.
  • string: the string for which the length is computed.

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.
  • string: the string on which to perform the replace operation.
  • target: the string that will be replaced.
  • replacement: the string value that will be used to replacetarget.

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.
  • year is a number representing the year ( 1 - 9999 ).
  • month is a number representing the month.
  • day is a number representing the day.
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).

  • text: this is a string.
  • format : <yyyy>, <mm>, <dd> combined freely as long as it matches the text.
 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.
  • unit is a string constant.
  • unit can be one of the following:
    • "day"
    • "month"
    • "year"

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.
  • interval: number of time interval to be added.
  • date: date value to be operated on.
  • unit is a string constant.
  • unit can be one of the following:
    • "day"
    • "month"
    • "year"

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 false

2 > 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.
  • number2: the number of decimal places (optional).

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.
  • number2: the number of decimal places (optional).
 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
Related Information