List of expressions
Aggregations
Aggregation expressions take into account all values in a field. They can only be used in the Summarize section of the notebook editor.
Average
Returns the average of the values in the column.
Syntax: Average(column)
Example: Average([Quantity]) returns the mean for the Quantity field.
Count
Returns the count of rows (also known as records) in the selected data.
Syntax: Count
Example: Count If a table or result returns 10 rows, Count will return 10.
CountIf
Only counts rows where the condition is true.
Syntax: CountIf(condition).
Example: CountIf([Subtotal] > 100) returns the number of rows where the subtotal is greater than 100.
CumulativeCount
The additive total of rows across a breakout.
Syntax: CumulativeCount.
Example: CumulativeCount.
CumulativeSum
The rolling sum of a column across a breakout.
Syntax: CumulativeSum(column).
Example: CumulativeSum([Subtotal]).
Distinct
The number of distinct values in a column.
Syntax: Distinct(column).
Example:Distinct([Last Name])returns the count of unique last names in the column. Duplicates (of the last name "Smith" for example) are not counted.
Max
Returns the largest value found in the column.
Syntax: Max(column).
Example: Max([Age]) returns the oldest age found across all values in the Age column.
Related: Min, Average, Median.
Median
Returns the median value of the specified column.
Syntax: Median(column).
Example: Median([Age]) finds the midpoint age where half of the ages are older, and half of the ages are younger.
Databases that don't support
median: SQLite, Vertica, SQL server, MySQL. Presto only provides approximate results.
Min
Returns the smallest value found in the column.
Syntax: Min(column).
Example: Min([Salary]) finds the lowest salary among all salaries in the Salary column.
Related: Max, Median, Average.
Percentile
Returns the value of the column at the percentile value.
Syntax: Percentile(column, percentile-value)
Example: Percentile([Score], 0.9) returns the value at the 90th percentile for all values in that column.
Databases that don't support
percentile: H2, MySQL, SQL Server, SQLite, Vertica. Presto only provides approximate results.
Share
Returns the percent of rows in the data that match the condition, as a decimal.
Syntax: Share(condition)
Example: Share([Color] = "Blue") returns the number of rows with the Color field set to Blue, divided by the total number of rows.
StandardDeviation
Calculates the standard deviation of the column, which is a measure of the variation in a set of values. A low standard deviation indicates values cluster around the mean, whereas a high standard deviation means the values are spread out over a wide range.
Syntax: StandardDeviation(column)
Example: StandardDeviation([Population]) returns the SD for the values in the Population column.
Sum
Adds up all the values of the column.
Syntax: Sum(column)
Example: Sum([Subtotal]) adds up all the values in the Subtotal column.
SumIf
Sums up the specified column only for rows where the condition is true.
Syntax: SumIf(column, condition).
Example:SumIf([Subtotal], [Order Status] = "Valid") adds up all the subtotals for orders with a status of "Valid".
Variance
Returns the numeric variance for a given column.
Syntax: Variance(column)
Example: Variance([Temperature]) returns a measure of the dispersion from the mean temperature for all temps in that column.
Related: StandardDeviation, Average.
Functions
Function expressions apply to each individual value. They can be used to alter or filter values in a column or create new, custom columns.
abs
Returns the absolute (positive) value of the specified column.
Syntax: abs(column)
Example: abs([Debt]). If Debt were -100, abs(-100) would return 100.
between
Checks a date or number column's values to see if they're within the specified range.
Syntax: between(column, start, end)
Example: between([Created At], "2019-01-01", "2020-12-31") would return rows where Created At date fell within the range of January 1, 2019 and December 31, 2020.
Related: interval.
case
Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.
Syntax: case(condition, output, …)
Example: case([Weight] > 200, "Large", [Weight] > 150, "Medium", "Small") If a Weight is 250, the expression would return "Large". In this case, the default value is "Small", so any Weight of 150 or less would return "Small".
ceil
Rounds a decimal up (ceil as in ceiling).
Syntax: ceil(column).
Example: ceil([Price]). ceil(2.99) would return 3.
coalesce
Looks at the values in each argument in order and returns the first non-null value for each row.
Syntax: coalesce(value1, value2, …)
Example: coalesce([Comments], [Notes], "No comments"). If both the Comments and Notes columns are null for that row, the expression will return the string "No comments".
concat
Combine two or more strings together.
Syntax: concat(value1, value2, …)
Example: concat([Last Name], ", ", [First Name]) would produce a string of the format "Last Name, First Name", like "Palazzo, Enrico".
contains
Checks to see if string1 contains a string2 within it.
Syntax: contains(string1, string2)
Example: contains([Status], "Class"). If Status were "Classified", the expression would return true.
Related: regexextract.
convertTimezone
Shifts a date or timestamp value into a specified timezone.
Syntax: convertTimezone(column, target, source).
Example: convertTimezone("2022-12-28T12:00:00", "Canada/Pacific", "Canada/Eastern") returns the value 2022-12-28T09:00:00, displayed as December 28, 2022, 9:00 AM.
datetimeAdd
Adds some unit of time to a date or timestamp value.
Syntax: datetimeAdd(column, amount, unit).
Example: datetimeAdd("2021-03-25", 1, "month") returns the value 2021-04-25, displayed as April 25, 2021.
Related: between, datetimeSubtract.
datetimeDiff
Returns the difference between two datetimes in some unit of time. For example, datetimeDiff(d1, d2, "day") returns the number of days between d1 and d2.
Syntax: datetimeDiff(datetime1, datetime2, unit).
Example: datetimeDiff("2022-02-01", "2022-03-01", "month") returns 1.
datetimeSubtract
Subtracts some unit of time from a date or timestamp value.
Syntax: datetimeSubtract(column, amount, unit).
Example: datetimeSubtract("2021-03-25", 1, "month") returns the value 2021-02-25, displayed as February 25, 2021.
Related: between, datetimeAdd.
day
Takes a datetime and returns the day of the month as an integer.
Syntax: day([datetime column]).
Example: day("2021-03-25T12:52:37") returns the day as an integer, 25.
endswith
Returns true if the end of the text matches the comparison text.
Syntax: endsWith(text, comparison)
endsWith([Appetite], "hungry")
Related: contains and startswith.
exp
Returns Euler's number, e, raised to the power of the supplied number.
Syntax: exp(column).
Example: exp([Interest Months])
Related: power.
floor
Rounds a decimal number down.
Syntax: floor(column)
Example: floor([Price]). If the Price were 1.99, the expression returns 1.
hour
Takes a datetime and returns the hour as an integer (0-23).
Syntax: hour([datetime column]).
Example: hour("2021-03-25T12:52:37") returns 12.
interval
Checks a date column's values to see if they're within the relative range.
Syntax: interval(column, number, text).
Example: interval([Created At], -1, "month").
Related: between.
isempty
Returns true if the column is empty.
Syntax: isempty(column)
Example: isempty([Discount]) returns true if there is no value in the discount field.
isnull
Returns true if the column is null.
Syntax: isnull(column)
Example: isnull([Tax]) returns true if no value is present in the column for that row.
lefttrim
Removes leading whitespace from a string of text.
Syntax: ltrim(text)
Example: ltrim([Comment]). If the comment were " I'd prefer not to", ltrim would return "I'd prefer not to".
length
Returns the number of characters in text.
Syntax: length(text)
Example: length([Comment]) If the comment were "wizard", length would return 6 (since "wizard" has six characters).
log
Returns the base 10 log of the number.
Syntax: log(column).
Example: log([Value]).
lower
Returns the string of text in lowercase.
Syntax: lower(text).
Example: lower([Status]). If the Status were "QUIET", the expression would return "quiet".
Related: upper.
minute
Takes a datetime and returns the minute as an integer (0-59).
Syntax: minute([datetime column]).
Example: minute("2021-03-25T12:52:37") would return 52.
month
Takes a datetime and returns the month number (1-12) as an integer.
Syntax: month([datetime column]).
Example: month("2021-03-25T12:52:37") returns the month as an integer, 3.
now
Returns the current date and time using your project report timezone.
Syntax: now.
power
Raises a number to the power of the exponent value.
Syntax: power(column, exponent).
Example: power([Length], 2). If the length were 3, the expression would return 9 (3 to the second power is 3*3).
Databases that don't support
power: SQLite.
Related: exp.
quarter
Takes a datetime and returns the number of the quarter in a year (1-4) as an integer.
Syntax: quarter([datetime column]).
Example: quarter("2021-03-25T12:52:37") would return 1 for the first quarter.
regexextract
Extracts matching substrings according to a regular expression.
Syntax: regexextract(text, regular_expression).
Example: regexextract([Address], "[0-9]+").
Databases that don't support
regexextract: H2, SQL Server, SQLite.
replace
Replaces all occurrences of a search text in the input text with the replacement text.
Syntax: replace(text, find, replace).
Example: replace([Title], "Enormous", "Gigantic").
righttrim
Removes trailing whitespace from a string of text.
Syntax: rtrim(text)
Example: rtrim([Comment]). If the comment were "Fear is the mind-killer. ", the expression would return "Fear is the mind-killer."
round
Rounds a decimal number either up or down to the nearest integer value.
Syntax: round(column).
Example: round([Temperature]). If the temp were 13.5 degrees centigrade, the expression would return 14.
Example: round([Temperature] * 10) / 10. If the temp were 100.75, the expression would return 100.8.
second
Takes a datetime and returns the number of seconds in the minute (0-59) as an integer.
Syntax: second([datetime column).
Example: second("2021-03-25T12:52:37") would return the integer 37.
sqrt
Returns the square root of a value.
Syntax: sqrt(column).
Example: sqrt([Hypotenuse]).
Databases that don't support
sqrt: SQLite.
Related: Power.
startswith
Returns true if the beginning of the text matches the comparison text.
Syntax: startsWith(text, comparison).
Example: startsWith([Course Name], "Computer Science") would return true for course names that began with "Computer Science", like "Computer Science 101: An introduction".
substring
Returns a portion of the supplied text, specified by a starting position and a length.
Syntax: substring(text, position, length)
Example: substring([Title], 1, 10) returns the first 10 letters of a string (the string index starts at position 1).
Related: regexextract, replace.
trim
Removes leading and trailing whitespace from a string of text.
Syntax: trim(text)
Example: trim([Comment]) will remove any whitespace characters on either side of a comment.
upper
Returns the text in all upper case.
Syntax: upper(text).
Example: upper([Status]). If Status were "hyper", upper("hyper") would return "HYPER".
week
Takes a datetime and returns the week as an integer.
Syntax: week(column, mode).
Example: week("2021-03-25T12:52:37") would return the week as an integer, 12.
- column: the name of the column of the date or datetime value.
- mode: Optional.
- ISO: (default) Week 1 starts on the Monday before the first Thursday of January.
- US: Week 1 starts on Jan 1. All other weeks start on Sunday.
- Instance: Week 1 starts on Jan 1. All other weeks start on the day defined in your project localization settings.
weekday
Takes a datetime and returns an integer (1-7) with the number of the day of the week.
Syntax: weekday(column)
- column: The datetime column.
Example:
case(
weekday([Created At]) = 1, "Sunday",
weekday([Created At]) = 2, "Monday",
weekday([Created At]) = 3, "Tuesday",
weekday([Created At]) = 4, "Wednesday",
weekday([Created At]) = 5, "Thursday",
weekday([Created At]) = 6, "Friday",
weekday([Created At]) = 7, "Saturday")
year
Takes a datetime and returns the year as an integer.
Syntax: year([datetime column]).
Example: year("2021-03-25T12:52:37") would return the year 2021 as an integer, 2,021.
Updated 6 months ago