Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
Functions available in the Functions Pane within the Calculated Field Editor are Numeric, Trigonometric or String.
Number functions are math functions most likely used on Measures. Trigonometric functions are for use with lengths and angles of triangles. String functions most commonly work with strings and text formatting of Dimensions. Table Calculation Functions are useful when working with Table Calculations. Logical Functions are useful for if conditions. Date Functions allow manipulating dates. Aggregation functions are for dealing with aggregated values, Type Conversions Functions are for converting data types.
ATTR(expression)
Returns the value of the given expression if it only has a single value for all rows in the group, otherwise it displays an asterisk (*) character. Null values are ignored.
Example: ATTR([State])
AVG(expression)
Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.
Example: AVG([Profit])
COUNT(expression)
Returns the number of items in a group. NULL values are not counted.
Example: COUNT([Customer Name])
COUNTD(expression)
Returns the distinct number of items in a group, excluding NULL.
Example: COUNTD([Customer Name])
FIRST(expression)
Returns the first of the values in the expression. LAST can be used with numeric fields only. Null values are ignored.
Examples: FIRST([Profit])
LAST(expression)
Returns the last of all the values in the expression. LAST can be used with numeric fields only. Null values are ignored.
Examples: LAST([Profit])
MAX(expression) or MAX(expr1, expr2)
Returns the maximum of a single expression across all records or the maximum of two expressions for each record.
Examples: MAX([Sales]) will return maximum value in the Sales field(Numeric fields only) MAX(2, 5, 1) will return 5 MAX([Date1], [Date2]) will return maximum value of these two fields per row
MEDIAN(expression)
Returns the median of the expression. MEDIAN can be used with numeric fields only. Null values are ignored.
Examples: MEDIAN([Profit])
MIN(expression) or MIN(expr1, expr2)
Returns the minimum of a single expression across all records or the minimum of two expressions for each record.
Examples: MIN([Profit]) will return minimum value in the Profit field(Numeric fields only) MIN(2, 5, 1) will return 1 MIN([Date1], [Date2]) will return minimum value of these two fields per row
STDEV(expression)
Returns the sample standard deviation of the expression.
Example: STDEV([Profit])
STDEVP(expression)
Returns the population standard deviation of the expression.
Example: STDEVP([Profit])
SUM(expression)
Returns the sum of all the values in the expression. SUM can be used with numeric fields only. Null values are ignored.
Example: SUM([Profit])
VAR(expression)
Returns the sample variance of the expression.
Example: VAR([Profit])
VARP(expression)
Returns the population variance of the expression.
Example: VARP([Profit])
ABS(X)
Returns the absolute value of a number, which will always be zero or a positive number
Example: ABS(-11.2) will return 11.2
CEIL(X)
Returns the nearest integer value greater than or equal to x
Example: CEIL(10.2) will return 11
CEILING(X)
Returns the nearest integer value greater than or equal to x
Example: CEILING(10.2) will return 11
E(X)
Returns the base of natural logarithm 2.71828183
Example: E() will return 2.71828183
EXP(X)
Returns the value of e (the base of natural logarithms - where e = 2.71828183) raised to the x power
Example: EXP(2) will return 7.38905609893065
FLOOR(X)
Returns the nearest integer value less than or equal to x. If number is already rounded, it simply returns the number unchanged
Example: FLOOR(43.9) will return 43
Example: FLOOR(-27.8) will return -28
Example: FLOOR(27) will return 27
LN(X)
Returns the natural logarithm (logarithm base e - 2.71828183) of x
Example: LN(25) will return 3.2188758248682006
LOG(X) or LOG(B,X)
With one argument, returns the natural logarithm (logarithm base e - 2.71828183) of x
With two arguments, returns the logarithm of x for the provided base b
Example: LOG(25) will return 3.2188758248682006
Example: LOG(4, 25) will return 2.321928094887362 with a base of 4
LOG10(X)
Returns the logarithm base 10 of x
Example: LOG10(25) will return 1.3979400086720375
LOG2(X)
Returns the logarithm base 2 of x
Example: LOG2(25) will return 4.643856189774724
MAX(X1,X2, ...)
Returns the maximum value among the provided arguments - at least 2 arguments
Example: MAX(2,5,1) will return 5
MIN(X1,X2, ...)
Returns the minimum value among the provided arguments - at least 2 arguments
Example: MIN(2,5,1) will return 1
MOD(N,M)
Returns n modulo m (the remainder of n divided by m)
Example: MOD(12, 5) will return 2
PI()
Returns the constant π (= 3.1415926535...), the ratio between the circumference and diameter of a circle
Example: Will return 3.141592653589793
POW(X)
Returns a "negative safe" square root of x, equivalent to SIGN(x) * SQRT(ABS(x))
Example: POW(16) will return 4
POWER(X, Y)
Return the value of x raised to the exponent y
Example: POWER(10, 3) will return 1000
ROUND(X)
or
ROUND(X, precision)
Returns the value of x rounded to the nearest integer if precision is not used. Otherwise rounds to the nearest decimal precision
Example: ROUND(PI()) will return 3
SAFELOG10(X)
Returns a "negative safe" logarithm base 10 of x, equivalent to SIGN(x) * LOG10(ABS(x))
Example: SAFELOG10(25) will return 1.3979400086720375
SAFESQRT(X)
Returns a "negative safe" square root of x, equivalent to SIGN(x)*SQRT(ABS(x))
Example: SAFESQRT(-16) will return -4
SIGN(X)
Returns the sign of x: 1 for positive, -1 for negative
Example: SIGN(-55) will return -1
Example: SIGN(55) will return 1
SQRT(X)
Returns the square root of x
Example: SQUARE(64) will return 8
SQUARE(X)
Returns the square of x
Example: SQUARE(8) will return 64
ZN(X)
Returns x if not null. If x is null will return 0
Example: ZN(25) will return 25
ACOS(X)
Returns the inverse cosine (arc cosine) of x. The argument x must be in the range of -1 to 1, and the function returns a value in the range of 0 to pi, expressed in radians
Example: ACOS(.5) will return 1.0471975511965979
ASIN(X)
Returns the inverse sine (arc sine) of x. The valid range for the ASIN() input argument is from -1 through 1.
Example: ASIN(-1) will return -1.5707963
ATAN(X)
Returns the inverse tangent (arc tangent) of x. Inputs are in an unbounded range, and the return value is given in radians, ranging from -pi/2 to pi/2
Example: ATAN(1) will return .785398163
ATAN2(Y,X)
For the Cartesian coordinates x, y return the polar coordinate angle theta. The argument x can be in an unbounded range and returns a value in the range of -pi to pi, depending on the signs of x and y, expressed in radians. ATAN2(n1,n2) is the same as ATAN2(n1/n2)
Example: ATAN2(1, 0) will return 1.5707963267948966
COS(X)
Returns the cosine of x(an angle expressed in radians)
Example: COS(38) will return 0.9550736440472949
COT(X)
Returns the cotangent of x. Represents the cotangent function cos(x)/sin(x)
Example: COT(20) will return 0.4469951089489167
DEGREES(X)
Converts x from radians to degrees
Example: DEGREES(PI()) will return 180
RADIANS(X)
Converts x from degrees to radians
Example: RADIANS(28.5) will return 0.4974188368183839
SIN(X)
Returns the sine in radians of x
Example: SIN(2.3) will return 0.7457052121767203
TAN(X)
Returns the tangent of x in radians
Example: TAN(.35) will return 0.36502849483042454
ASCII(str)
Converts the first character of the given string str into the integer ASCII value
Example: ASCII("a") will return 97
CAP(str)
Capitalize words in the string str. Individual words/names will be given uppercase first letters, with all other letters in lowercase
Example: CAP("the house") will return "The House"
CHAR(int)
Converts the given integer ASCII code int into a character
Example: CHAR(65) will return "A"
CONCAT(str1, str2, ...)
Concatenate the input strings into one resulting string
Example: CONCAT("Here", "we", "go") will return "Herewego"
CONCAT_WS(separator, str1, str2, ...)
Concatenate with separator. Concatenates the input strings into one resulting string, placing the string separator between each of the other arguments
Example: CONCAT_WS("-","Here", "we", "go") will return "Here-we-go"
CONTAINS(substr, str)
Tests if the string str contains the specified substring substr. Returns false if the string doesn't contain the substring
Example: CONTAINS("da", "Have a great day") will return true
Example: CONTAINS("test", "Have a great day") will return false
ENDSWITH(substr, str)
Tests if the string str ends with the specified substring substr. Returns false if the string doesn't end with substring
Example: ENDSWITH("ay", "Have a great day") will return true
Example: ENDSWITH("test", "Have a great day") will return false
FIND(substr, str, [start])
Returns the starting position of the first occurrence of substring substr in the string str. Returns 0 if the substring is not found
Example: FIND("great", "Have a great day", 3) will return 8
Example: FIND("test", "Have a great day") will return 0
FORMAT(X,D)
Format the number x as a string of the type "#,###.##", showing d decimal places
Example: FORMAT(12332.123456, 4) will return 12,332.1235
INSERT(str, pos, len, newstr)
Replaces the substring of length len starting at position pos in input string str with the string newstr
Example: INSERT("THIS", 1, 4, "THAT") will return "THAT"
LCASE(str)
Returns the string str mapped to lowercase letters
Example: LCASE("California") will return "california"
LEFT(str, len)
Returns the leftmost len characters of string str
Example: LEFT("Aquafold", 4) will return "Aqua"
LENGTH(str)
Returns the length, in characters, of the input string str
Example: LENGTH("Sunnyvale") will return 9
LOWER(str)
Returns the string str mapped to lowercase letters
Example: LOWER("California") will return "california"
LPAD(str, len, padstr)
Pad the left side of string str with copies of string pad, up to a total padding of len characters
Example: LPAD("California", 15, "X" ) will return "XXXXXCalifornia"
LTRIM(str)
Returns a copy of the string with leading whitespace omitted.
Example: LTRIM(" Happy ") will return "Happy "
MID(str, pos) or MID(str, pos, len)
For two arguments, returns the substring of str starting at position pos and continuing to the end of the string
For three arguments, returns the substring of str of length len, beginning at position pos
Example: MID("Have a happy day", 8) will return "happy day"
Example: MID("Have a happy day", 8, 5) will return "happy"
POSITION(substr, str)
Returns the starting position of the first occurrence of substring substr in the string str. Returns 0 if the substring is not found
Example: POSITION("great", "Have a great day") will return 8
Example: POSITION("test", "Have a great day") will return 0
REPEAT(str, count)
Returns a string consisting of str repeated count times
Example: REPEAT("California", 3) will return "CaliforniaCaliforniaCalifornia"
REPLACE(str, from_str, to_str)
Returns a copy of str in which all occurrences of from_str have been replaced by to_str
Example: REPLACE("Have a happy day", "happy", "great") will return "Have a great day"
REVERSE(str)
Returns a reversed copy of the input string str
Example: REVERSE("New York") will return "kroY weN"
RIGHT(str, len)
Returns the len rightmost characters of string str
Example: RIGHT("Happy Day", 3) will return "Day"
RPAD(str, len, padstr)
Pad the right side of string str with copies of string pad, up to a total padding of len characters
RTRIM(str)
Returns a copy of the string with trailing whitespace omitted.
Example: RTRIM(" Happy ") will return " Happy"
SPACE(N)
Returns a string consisting of n whitespace characters
Example: SPACE(12) will return " "
STARTSWITH(substr, str)
Tests if the string str starts with the specified substring substr. Returns false if the string doesn't start with substring
Example: STARTSWITH("Ha", "Have a great day") will return true
Example: STARTSWITH("test", "Have a great day") will return false
SUBSTRING(str, pos) or SUBSTRING(str, pos, len)
For two arguments, returns the substring of str starting at position pos and continuing to the end of the string
For three arguments, returns the substring of str of length len, beginning at position pos
Example: SUBSTRING("Have a happy day", 8) will return "happy day"
Example: SUBSTRING("Have a happy day", 8, 5) will return "happy"
TRIM(str)
Returns a copy of the string with leading and trailing whitespace omitted.
Example: TRIM(" Happy ") will return "Happy"
UCASE(str)
Returns the string str mapped to uppercase letters
Example: UCASE("Hawaii") will return "HAWAII"
UPPER(str)
Returns the string str mapped to uppercase letters
Example: UPPER("Hawaii") will return "HAWAII"
DATE
Converts value to a Date based on locale. Returns null if conversion can't be done.
Example: DATE(18000) will return 4/14/2019
Example: DATE([INTEGER FIELD])
DATEADD(part, increment, date)
Increments the selected part of the given date and returns a new date. Part options: "Month", "Day", "Year", "Dayofyear", "Weekofyear", "Hour", "Minute", "Second", "Quarter".
Date format example: 1/7/2015 2:48:02 PM or [Date field]
Example: DATEADD("Hour", 2, "1/7/2015 2:48:02 PM") will return 1/7/2015 4:48:02 PM
Example: DATEADD("Year", 2, [DATE FIELD])
DATEDIFF(part, date1, date2)
Returns the difference between date1 and date2 based on the provided part of the date as a positive or negative integer.
Part options: "Quarter", "Year", "Month", "Week", "Day", "Hour", "Minute", "Second".
Date format example: 1/7/2015 2:57:25 PM or [Date field]
Example: DATEDIFF("Hour", "1/8/2015 4:57:25 PM", "1/7/2015 2:57:25 PM") will return -26
Example: DATEDIFF("Hour", [STARTDATE], [ENDDATE])
DATENAME(part, date)
Returns the selected part of the date as a string for the given date.
Part options: "Month", "Day", "Year", "Dayofyear", "Weekofyear", "Hour", "Minute", "Second", "Quarter", "AMorPM".
Date format example: 1/7/2015 3:01:32 PM or [Date field]
Example: DATENAME("Month", "1/7/2015") will return January
Example: DATENAME("Year", [DATE FIELD])
DATEPARSE
Converts a string date into a datetime data type using the specified format pattern.
Pattern character options:
"D Day in year",
"E Day name in week",
"F Day of week in month",
"G Era designator",
"H Hour in day (0-23)",
"K Hour in am/pm (0-11)",
"M Month in year",
"S Millisecond",
"W Week in month",
"X Time zone",
"Y Week year",
"Z Time zone",
"a Am/pm marker",
"d Day in month",
"h Hour in am/pm (1-12)",
"k Hour in day (1-24)",
"m Minute in hour",
"s Second in minute",
"u Day number of week",
"w Week in year",
"y Year year",
"z Time zone"
Example: DATEPARSE("MM/dd/yyyy hh:mm:ss a" , "1/7/2015 3:08:31 PM")
Example: DATEPARSE("EEE, d MMM yyyy HH:mm:ss Z", [STRING DATE FIELD])"
DATEPART(part, date)
Returns the selected part of the date as an integer for the given date.
Part options: "Month", "Day", "Year", "Dayofyear", "Weekofyear", "Hour", "Minute", "Second", "Quarter", "AMorPM".
Date format example: 1/7/2015 3:05:27 PM or [Date field]
Example: DATEPART("Quarter", "1/7/2015") will return 1
Example: DATEPART("Year", [DATE FIELD])
DATETRUNC(part, date)
Returns a date where the selected part of the date is most significant field of the given date.
Part options: "Year", "Month", "Day", "Hour", "Minute", "Second".
Date format example: 1/7/2015 3:16:41 PM or [Date field]
Example: DATETRUNC("Hour", "1/7/2015 3:16:41 PM") will return 2015-01-07 15:00:00.0)
Example: DATETRUNC("Year", [DATE FIELD])
DAY(date)
Returns the day for the given date as an integer.
Date format example: 1/7/2015
Example: DAY("1/7/2015") will return 7
ISDATE(str)
Returns true if the string field str can be converted to a date based on locale, otherwise false
Example: ISDATE("1/7/2015") will return true
Example: ISDATE([STRING FIELD])
MONTH(date)
Returns the month for the given date as an integer.
Date format example: 1/7/2015
Example: MONTH("1/7/2015") will return 1
TODAY()
Returns the current date
Example: TODAY() will return the current date like "1/7/2015"
YEAR(date)
Returns the year for the given date as an integer.
Date format example: 1/7/2015
Example: YEAR("1/7/2015") will return 2015
IFNULL(value1, value2)
Returns value1 if value1 is not null, otherwise returns value2. Both value1 and value2 must be the same type. For example, String
Example: IFNULL(null, "Hello") will return "Hello"
Example: IFNULL([STRING FIELD], "Hello")
Example: IFNULL([INTEGER FIELD], 97)
IIF(test predicate, then(true), else(false), [unknown])
Evaluates the test predicate as a boolean and returns the object in the then position if true. Otherwise, if false, returns the object in the else position. If the test predicate returns a null, the object in the unknown position is returned - if supplied, otherwise null is returned. All three objects - then, else and unknown, must be the same type. For example, String
Example: IIF((1 > 0) && (3 > 4), "Hello", "Goodbye") will return "Goodbye"
Example: IIF([INTEGER FIELD] > 100, 1, 2, 3)
Example: IIF([STRING FIELD] = "New York", true, false, false)
ISDATE(str)
Returns true if the string field str can be converted to a date based on locale, otherwise false
Example: ISDATE("1/7/2015") will return true
Example: ISDATE([STRING FIELD])
ISNULL(value)
Returns true if the value is null, otherwise false
Example: ISNULL(null) will return true
Example: ISNULL([FIELD]
INDEX()
Returns the index of the current row in the partition, without any sorting with regard to value.
Example: INDEX()
FIRST_P()
Returns the number of rows from the current row to the first row in the partition.
Example: FIRST_P()
LAST_P()
Returns the number of rows from the current row to the last row in the partition.
Example: LAST_P()
LOOKUP
Returns the value of the expression in a target row, specified as relative offset from the current row. Use FIRST() + n and LAST - n as part of your offset definition for a target relative to the first/last rows in the partition.
Example: LOOKUP(SUM([Profit]), 2)
SCRIPT_INT("<R expression>", arg1 ...)
Returns an integer result from a given R expression. The R expression is forwarded to a running Rserve instance. Use .arg# inside the R expression to reference parameters.
Example: SCRIPT_INT("sort(.arg1)", SUM([Profit]))
SCRIPT_REAL("<R expression>", arg1 ...)
Returns a numeric result from a given R expression. The R expression is forwarded to a running Rserve instance. Use .arg# inside the R expression to reference parameters.
Example: SCRIPT_REAL("mean(.arg1)", SUM([Profit]))
DATE(value)
Converts value to a Date based on locale. Returns null if conversion can't be done
Examples: DATE(18000) will return 4/13/2019
DATE([INTEGER FIELD])
DATETIME(value)
Converts value to Date and Time based on locale. Returns null if conversion can't be done
Examples: DATETIME(18000) will return 4/13/2019 5:00:00 PM
DATETIME([INTEGER FIELD])
FLOAT(value)
Converts value to a Float
Examples: FLOAT("27.99876") will return 27.99876
FLOAT([STRING FIELD])
INTEGER(value)
Converts value to an Integer
Examples: INTEGER(6.3 * 4.7) will return 29
INTEGER([FLOAT FIELD])
STR(value)
Converts value to a String
Examples: STR(47 * 3) will return 141
STR([INTEGER FIELD])