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, Math Curve Functions are technical indicators, Math Financial Functions are for comparing data of a set of securities and risk analysis.
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])
DETREND(y) or DETREND(x, y)
Removes linear trend from incoming data set.
Example: DETREND(MAX([High]))
or DETREND(MAX([Open]), MAX([Close]))
EXPFIT(y) or EXPFIT(x, y)
Calculates exponential fit for incoming array with no initial guess (linear transform fit).
Example: EXPFIT(MAX([High]))
or EXPFIT(MAX([Low]), MAX([High]))
For more information, see Linearization.
LINFIT(y) or LINFIT(x, y)
Calculates best fit line through incoming data.
Example: LINFIT(MIN([High]))
or LINFIT(MIN([Low], MIN[High]))
For more information, see Linear Regressing.
LOGFIT(y) or LOGFIT(x, y)
Calculates logarithmic fit for incoming array with no initial guess (linear transform fit).
Example: LOGFIT(MAX([Volume]))
or LOGFIT(MAX([Open]), MAX([Volume]))
For more information, see Linearization.
POLYFIT(y, order) or POLYFIT(x, y, order)
Calculates best-fit Polynomial of user-defined order.
Example: POLYFIT(MAX([High]), 3)
or POLYFIT(MAX([Volume]), MAX([High]), 3)
For more information, see Polynomial Regression.
POWERFIT(y) or POWERFIT(x, y)
Calculates power fit for incoming array with no initial guess, (form = a xb+c).
Example: POWERFIT(MAX([High]))
or POWERFIT(MAX([Volume]), MAX([High]))
For more information, see Nonlinear Regression.
SGFilter(x,nl,nr,order)
Returns data smoothed via least-squares polynomial of user-specified order in a window specified by pointsBefore(nl) and pointsAfter(nr) the current row.
Example: SGFILTER(MAX([High]), 5, 2, 2)
For more information, see Savitzky-Goyal Filter.
SMOOTH(in,span)
Smoothes incoming data set via centered running mean of given window length.
Example: SMOOTH(MAX([High]), 1)
For more information, see Moving Average.
ACCUMDISTOSC(high,low, close, volume)
Calculates Accumulation Distribution (Chaikin) Oscillator for incoming data. Finds relative difference between high, low, and close and scaled by volume. Determines whether investors are generally buying or selling.
Example: ACCUMDISTOSC(MAX([High]), MIN([Low]), LAST([Close]), LAST([Volume]))
For more information, see Accumulation/Distribution Index.
ACCUMDISTRIB(high,low, close, volume)
Calculates accumulation/ distribution line of incoming data. Finds relative difference between high, low, and close and scaled by volume. Determines whether investors are generally buying or selling.
Example: ACCUMDISTRIB(MAX([High]), MIN([Low]), LAST([Close]), LAST([Volume]))
For more information, see Accumulation/Distribution Index.
BOLLINGER_ABOVE(in,span)
Returns Bollinger above bands for incoming data.
Example: BOLLINGER_ABOVE(MIN([High]), 1)
For more information, see Bollinger Bands.
BOLLINGER_BELOW(in,span)
Returns Bollinger below bands for incoming data.
Example: BOLLINGER_BELOW(MIN([High]), 1)
For more information, see Bollinger Bands.
CHAIKINMONEYFLOW(high,low,close,volume ,span)
Calculates Chaikin Money Flow for incoming data. Compares closing price to price range and determines if data is under accumulation or distribution.
Example: CHAIKINMONEYFLOW(MAX([High]), MIN([Low]), LAST([Close]), LAST([Volume]), 9)
For more information, see Money Flow Index.
CHAIKINOSC(high,low, close, volume)
Calculates Chaikin Oscillator for incoming data.
Example: CHAIKINOSC(MAX([High]), MIN([Low]), LAST([Close]), LAST([Volume]))
For more information, see Accumulation/Distribution Index.
CHVOL(high,low,span)
Calculates Chaikin volitility. Calculates range between high and low for given period.
Example: CHVOL(MAX([High]), MIN([Low]), 1)
For more information, see Chaikin Volatility.
CLV(High, Low, Close)
Calculates close location value of incoming data. Scales the closing value of a stock to the difference between high and low prices.
Example: CLV(1, 0, 1) will return 1.5707963267948966
or CLV(MAX([High]), MIN([Low]), LAST([Low]))
For more information, see Accumulation/Distribution Index.
EMA(in,span)
Calculates Exponential moving average. moving average whose weighting factors decrease exponentially.
Example: EMA(MAX([High]) , 1)
For more information, see Moving Average.
HHIGH(close,span)
Calculates highest high in a given period.
Example: HHIGH(LAST([Close]), 1)
For more information, see
LLOW(close,span)
Calculates highest high in a given period.
Example: LLOW(LAST([Close]), 1))
For more information, see
MACD(in,fastspan,slowspan,convspan)
Returns moving average convergence/divergence for incoming data. Calculates the difference between fast and slow exponential moving averages of incoming data.
Example: MACD(SUM([High]), 4, 3, 2)
For more information, see MACD.
MEDIANPRICE(high,low)
Returns the Median price of the expression.
Example: MEDIANPRICE(MAX([High]), MIN([Low]))
For more information, see Median.
MOMENTUM(close,span)
Calculates momentum of incoming data. Finds change in price over period.
Example: MOMENTUM(LAST([Close]), 1)
For more information, see Momentum.
MOVINGAVERAGE(expression, offset)
Calculates Simple Moving average.
Example: MOVINGAVERAGE(SUM([Profit]),9)
For more information, see Simple Moving Average.
MOVINGSTD(in,span)
Calculates Moving Standard Deviation. Measures variability of data during period.
Example: MOVINGSTD(MIN([High]), 1)
For more information, see Standard Deviation.
NVI(volume,close)
Returns negative volume index of incoming data. Index which tracks days which close lower than the previous day.
Example: NVI(LAST([Volume]), LAST([Close]))
For more information, see Negative Volume Index.
OBV(volume,close)
Returns the balance volume of expression. Accumulator relating momentum to volume.
Example: OBV(LAST([Volume]), LAST([Close]))
For more information, see On-Balance Volume.
PROC(close,span)
Returns price rate of change of incoming data. Calculates the relative change in price over a given period.
Example: PROC(LAST([Close]) , 1)
For more information, see Momentum.
PVI(volume,close)
Returns positive volume index of expression. Index which tracks days which close higher than the previous day.
Example: PVI(LAST([Volume]), LAST([Close]))
For more information, see Negative Volume Index.
PVT(volume,close)
Calculates price and volume trend of expression. Accumulator of daily volume, scaled by relative change in closing price.
Example: PVT(LAST([Volume]), LAST([Close]))
For more information, see Volume-Price Trend.
RSI(close,span)
Calculates Relative strength index of incoming data. The ratio of the exponential moving averages of upward and downward movements of closing price.
Example: RSI(LAST([Close]), 1)
For more information, see Relative Strength Index.
STOCHOSC(high,low,close) or STOCHOSC(high,low,close,span) or STOCHOSC(high,low,close,span,%Dspan) or STOCHOSC(high,low,close,span,%Dspan,%KSpan)
Calculates %K line from default fast stochastic oscillator.
Calculates %K line from user defined fast stochastic oscillator.
Calculates %K line from user defined slow stochastic oscillator.
Example: STOCHOSC(MAX([High]), MIN([Low]), LAST([Close]))
or STOCHOSC(MAX([High]), MIN([Low]), LAST([Close]), 14)
or STOCHOSC(MAX([High]), MIN([Low]), LAST([Close]), 14, 3)
or STOCHOSC(MAX([High]), MIN([Low]), SUM([Close]), 14, 3, 3)
For more information, see Stochastic Oscillator.
STOCHOSCD(high,low,close) or STOCHOSCD(high,low,close,span) or STOCHOSCD(high,low,close,span,%Dspan) or STOCHOSCD(high,low,close,span,%Dspan,%KSpan)
Calculates %D line from default fast stochastic oscillator.
Calculates %D line from user defined fast stochastic oscillator.
Calculates %D line from user defined slow stochastic oscillator.
Example: STOCHOSCD(MAX([High]), MIN([Low]), LAST([Close]))
or STOCHOSCD(MAX([High]), MIN([Low]), LAST([Close]), 14)
or STOCHOSCD(MAX([High]), MIN([Low]), LAST([Close]), 14, 3)
or STOCHOSCD(MAX([High]), MIN([Low]), SUM([Close]), 14, 3, 3)
For more information, see Stochastic Oscillator.
TRH(high,close)
Returns True Range High of expression. Daily high or previous close, whichever is higher.
Example: TRL(MAX([High]), LAST([Close]))
For more information, see Average True Range.
TRL(low,close)
Returns True Range Low of expression. Daily low or previous close, whichever is lower.
Example: TRL(MIN([Low]), LAST([Close]))
For more information, see Average True Range.
TYPICALPRICE(high,low,close)
Returns the Typical price of the expression.
Example: TYPICALPRICE(MAX([High]), MIN([Low]), LAST([Close]))
For more information, see Typical Price.
VROC(volume,span)
Calculates volume rate of change. Calculates the relative change in volume over a given period.
Example: VROC(LAST([Close]) , 1)
For more information, see Momentum.
WACCUMDISTRIB(high,low, close)
Calculates Williams accumulation/distribution of incoming data. Accumulator of daily positive and negative price movments.
Example: WACCUMDISTRIB(MAX([High]), MIN([Low]), LAST([Close]))
For more information, see Williams Accumulation Distribution.
WCLOSE(high,low,close)
Calculates weighted close. Running average of daily price with additional weighting for the close.
Example: WCLOSE(MAX([High]), MIN([Low]), LAST([Close]))
For more information, see Weighted Close.
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
REGEXP_EXTRACT(str, regex) or REGEXP_EXTRACT(str, regex, index)
Returns a substring from a string that matches the regular expression pattern. If N is the index, it returns the Nth grouped substring in the string that matches the regular expression pattern.
Example: REGEXP_EXTRACT("800-123-4567", "([0-9]{3})-[0-9]{3}-[0-9]{4}") will return "800"
or REGEXP_EXTRACT("800-123-4567", "([0-9]{3})-([0-9]{3}-[0-9]{4})", 2) will return "123-4567"
REGEXP_MATCH(str, regex)
Returns true if the string matches the regular expression pattern.
Example: REGEXP_MATCH("800-123-4567", "[0-9]{3}-[0-9]{3}-[0-9]{4}") will return "true"
REGEXP_REPLACE(str, regex, str1)
Returns a copy of the specified string where the replacement string replaces the regular expression pattern.
Example: REGEXP_REPLACE("May 2000", "[0-9]{4}", "2015") will return "May 2015"
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 " "
SPLIT(str, delimiter, token number)
Returns a substring of a string based on a delimiter and token number. If the token number is negative, the string is counted from the right. If the token number is positive, the string is counted from the left.
Example: SPLIT("This_is_valid_data", "_", -2) will return "valid"
or SPLIT("This_is_valid_data", "_", 2) will return "is"
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])
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])
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
AND(IF <expr1> AND <expr2> THEN <result>)
Performs logical conjunction on two expressions.
Example: IF [Currency Code] = "AUD" AND [Freight] = 89.46 THEN "True" ELSE "False"
CASE WHEN THEN ... [ELSE ] END
Compare expressions, to return a matching value. If the expression does not match, it returns the default value. If the default value is not specified, it returns Null.
Example: CASE [RomanNumeral] WHEN "I" THEN 1 WHEN "II" THEN 2 ELSE 3 END
Example on String: CASE [Card Type] WHEN “Vista” THEN LOWER(“Vista”) WHEN “Superior Card” THEN UPPER(“Superior Card”) END
Example on Number: CASE [Profit] WHEN -1408.31 THEN ABS(-1408.31) WHEN 10 THEN SQUARE(10) WHEN 25 THEN SQRT(25) ELSE 0 END
Example on Date: CASE [Due Date] WHEN TODAY() THEN MONTH([DueDate]) ELSE YEAR([DueDate]) END
IF <expr> THEN <result> [ELSE IF <expr2> THEN <result2> ...] ELSE <else_result>
Test series of expressions, when expression evaluates to true then it execute THEN part of that expression otherwise it execute ELSE part.
Examples:IF [Stud_Percentage] > 80 THEN "A Grade" ELSE IF [Stud_Percentage] > 60 THEN "B Grade" ELSE "C Grade"
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]
IN(expression, expression [,expression])
Returns TRUE if the first argument is found in the list of remaining arguments, otherwise returns FALSE.
Example: IN([Currency Code] , "AUD" , "DEM") will return "TRUE" if the current value of Currency Code is either "AUD" or "DEM"
MATCH(expression, expression [,expression])
Returns value of the first argument if found in the list of remaining arguments, otherwise returns empty List.
Example: MATCH([Currency Code] , "AUD" , "DEM") will return "AUD" or "DEM" if the current value of Currency Code is "AUD" or "DEM"
NOT(IF <expr1> NOT <expr2> THEN <result>)
Performs logical negation on two expressions.
Example: IF NOT ( [Card Type] = "Vista" OR [Card Type] = "null" ) THEN "True" ELSE "False"
OR(IF <expr1> OR <expr2> THEN <result>)
Performs logical disjunction on two expressions.
Example: IF [Currency Code] = "AUD" OR [Currency Code] = "CAD" THEN "True" ELSE "False"
XOR(IF <expr1> XOR <expr2> THEN <result>)
Performs exclusive disjunction on two expressions.
Example: IF [Currency Code]= "AUD" XOR [Card Type] = "Vista" then "True" ELSE "False"
To compare a date field with a specific date value, you must ensure that the specified data value is also a date field. For example, IF ([OrderDate] = "8/8/2005" ) THEN 5 ELSE 10 is an invalid expression because you are comparing a Date field with a string. To convert the date to a string, you can use the DATEPARSE function. For example, IF ([OrderDate] = DATEPARSE("MM/dd/yyyy" , "8/8/2005")) THEN 5 ELSE 10. The same condition is also applied for CASE statements. For example, CASE ([OrderDate]) WHEN (DATEPARSE("MM/dd/yyyy" , "8/8/2005")) THEN 5 ELSE 10
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])
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])
RUNNING_AVG(expression)
Returns the average value of the expression from the first row in the partition to the current row.
Example: RUNNING_AVG(SUM([Profit])) computes the average value of SUM([Profit]) from the first row to the current row.
RUNNING_COUNT(expression)
Returns the number of values of the expression from the first row in the partition to the current row.
Example: RUNNING_COUNT(SUM([Profit])) computes the number of values of SUM([Profit] from the first row to the current row.
RUNNING_MAX(expression)
Returns the average value of the expression from the first row in the partition to the current row.
Example: RUNNING_MAX(SUM([Profit])) computes the maximum value of SUM([Profit] from the first row to the current row.
RUNNING_MIN(expression)
Returns the minimum value of the expression from the first row in the partition to the current row.
Example: RUNNING_MIN(SUM([Profit])) computes the minimum value of SUM([Profit]) from the first row to the current row.
RUNNING_SUM(expression)
Returns the SUM of the expression from the first row in the partition to the current row.
Example: RUNNING_SUM(SUM([Profit])) computes the sum of SUM([Profit]) from the first row to the current row.
STR(value)
Converts value to a String
Examples: STR(47 * 3) will return 141
STR([INTEGER FIELD])
WINDOW_AVG(expression, [pointsBefore, pointsAfter])
Returns the average value of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the average value for the entire partition is returned.
Example: WINDOW_AVG(SUM([Profit]), -9 , 5) computes the average value of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_COUNT(expression, [pointsBefore, pointsAfter])
Returns the number of values of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the number of values for the entire partition is returned.
Example: WINDOW_COUNT(SUM([Profit]), -9 , 5) computes the number of values of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_MAX(expression, [pointsBefore, pointsAfter])
Returns the maximum value of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the maximum value for the entire partition is returned.
Example: WINDOW_MAX(SUM([Profit]), -9 , 5) computes the maximum value of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_MEDIAN(expression, [pointsBefore, pointsAfter])
Returns the median value of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the median value for the entire partition is returned.
Example: WINDOW_MEDIAN(SUM([Profit]), -9 , 5) computes the median value of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_MIN(expression, [pointsBefore, pointsAfter])
Returns the minimum value of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the minimum value for the entire partition is returned.
Example: WINDOW_MIN(SUM([Profit]), -9 , 5) computes the minimum value of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_PERCENTILE(expression, [pointsBefore, pointsAfter], percentileValue)
Returns the percentile value of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the maximum value for the entire partition is returned.
Example: WINDOW_PERCENTILE(SUM([Profit]), -9 , 5 , 50) computes the 50th percentile of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_STDDEV(expression, [pointsBefore, pointsAfter])
Returns the standard deviation of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the standard deviation for the entire partition is returned.
Example: Window_STDDEV(SUM([Profit]), -9 , 5) computes the standard deviation of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_STDDEVP(expression, [pointsBefore, pointsAfter])
Returns the population standard deviation of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the population standard deviation for the entire partition is returned.
Example: Window_STDDEVP(SUM([Profit]), -9 , 5) computes the population standard deviation of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_SUM(expression, [pointsBefore, pointsAfter])
Returns the sum of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the sum for the entire partition is returned.
Example: WINDOW_SUM(SUM([Profit]), -9 , 5) computes the sum of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_VAR(expression, [pointsBefore, pointsAfter])
Returns the variance of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If pointsBefore and pointsAfter are not specified, the variance for the entire partition is returned.
Example: WINDOW_VAR(SUM([Profit]), -9 , 5) computes the variance of SUM([Profit]) in the 9 rows before the current row to 5 rows after.
WINDOW_VARP(expression, [pointsBefore, pointsAfter])
Returns the population variance of the expression in a window specified by points Before and points After the current row. Use FIRST_P() + n and LAST_P() - n as part of your offset definition to begin or end the window relative to the first/last rows in the partition. If points Before and points After are not specified, the population variance for the entire partition is returned.
Example: WINDOW_VARP(SUM([Profit]), -9 , 5) computes the population variance of SUM([Profit]) in the 9 rows before the current row to 5 rows after.