v0.43 / Users Guide / List of expressions

List of expressions

For an introduction to expressions, check out Writing expressions in the notebook editor.


Aggregation expressions take into account all values in a field. They can only be used in the Summarize section of the notebook editor.


Returns the average of the values in the column.

Syntax: Average(column)

Example: Average([Quantity]) would return the mean for the Quantity field.


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.


Only counts rows where the condition is true.

Syntax: CountIf(condition).

Example: CountIf([Subtotal] > 100) would return the number of rows where the subtotal were greater than 100.


The additive total of rows across a breakout.

Syntax: CumulativeCount.

Example: CumulativeCount.


The rolling sum of a column across a breakout.

Syntax: CumulativeSum(column).

Example: CumulativeSum([Subtotal]).

Related: Sum and SumIf.


The number of distinct values in this column.

Syntax: Distinct(column).

Distinct([Last Name]). Returns the count of unique last names in the column. Duplicates (of the last name “Smith” for example) are not counted.


Returns the largest value found in the column.

Syntax: Max(column).

Example: Max([Age]) would return the oldest age found across all values in the Age column.

Related: Min, Average, Median.


Returns the median value of the specified column.

Syntax: Median(column).

Example: Median([Age]) would find 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.

Related: Min, Max, Average.


Returns the smallest value found in the column.

Syntax: Min(column).

Example: Min([Salary]) would find the lowest salary among all salaries in the Salary column.

Related: Max, Median, Average.


Returns the value of the column at the percentile value.

Syntax: Percentile(column, percentile-value)

Example: Percentile([Score], 0.9) would return 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.


Returns the percent of rows in the data that match the condition, as a decimal.

Syntax: Share(condition)

Example: Share([Color] = "Blue") would return the number of rows with the Color field set to Blue, divided by the total number of rows.


Calculates the standard deviation of the column, which is a measure of the variation in a set of values. 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]) would return the SD for the values in the Population column.


Adds up all the values of the column.

Syntax: Sum(column)

Example: Sum([Subtotal]) would add up all the values in the Subtotal column.


Sums up the specified column only for rows where the condition is true.

Syntax: SumIf(column, condition).

Example:SumIf([Subtotal], [Order Status] = "Valid") would add up all the subtotals for orders with a status of “Valid”.


Returns the numeric variance for a given column.

Syntax: Variance(column)

Example: Variance([Temperature]) will return a measure of the dispersion from the mean temperature for all temps in that column.

Related: StandardDeviation, Average.


Function expressions apply to each individual value. They can be used to alter or filter values in a column, or create new, custom columns.


Returns the absolute (positive) value of the specified column.

Syntax: abs(column)

Example: abs([Debt]). If Debt were -100, abs(-100) would return 100.


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.


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 150 or less would return “Small”.


Rounds a decimal up (ciel as in ceiling).

Syntax: ceil(column).

Example: ceil([Price]). ceil(2.99) would return 3.

Related: floor, round.


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”.


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”.


Checks to see if string1 contains string2 within it.

Syntax: contains(string1, string2)

Example: contains([Status], "Class"). If Status were “Classified”, the expression would return true.

Related: regexextract.


Returns true if the end of the text matches the comparison text.

Syntax: endsWith(text, comparison)

endsWith([Appetite], "hungry")

Related: contains and startswith.


Returns Euler’s number, e, raised to the power of the supplied number. (Euler sounds like “Oy-ler”).

Syntax: exp(column).

Example: exp([Interest Months])

Related: power.


Rounds a decimal number down.

Syntax: floor(column)

Example: floor([Price]). If the Price were 1.99, the expression would return 1.

Related: ceil, round.


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.


Returns true if the column is empty.

Syntax: isempty(column)

Example: isempty([Discount]) would return true if there were no value in the discount field.


Returns true if the column is null.

Syntax: isnull(column)

Example: isnull([Tax]) would return true if no value were present in the column for that row.


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”.

Related: trim and righttrim.


Returns the number of characters in text.

Syntax: length(text)

Example: length([Comment]) If the comment were “wizard”, length would return 6 (“wizard” has six characters).


Returns the base 10 log of the number.

Syntax: log(column).

Example: log([Value]).


Returns the string of text in all lower case.

Syntax: lower(text).

Example: lower([Status]). If the Status were “QUIET”, the expression would return “quiet”.

Related: upper.


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.


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.

Related: contains.


Replaces a part of the input text with new text.

Syntax: replace(text, find, replace).

Example: replace([Title], "Enormous", "Gigantic").


Removes trailing whitespace from a string of text.

Syntax: rtrim(text)

Example: rtrim([Comment]). If the comment were “Fear is the mindkiller. “, the expression would return “Fear is the mindkiller.”

Related: trim and lefttrim.


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.


Returns the square root of a value.

Syntax: sqrt(column).

Example: sqrt([Hypotenuse]).

Databases that don’t support sqrt: SQLite.

Related: Power.


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”.

Related: endswith, contains.


Returns a portion of the supplied text, specified by a starting position and a length.

Syntax: substring(text, position, length)

Example: substring([Title], 0, 10) returns the first 11 letters of a string (the string index starts at position 0).

Related: replace.


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.


Returns the text in all upper case.

Syntax: upper(text).

Example: upper([Status]). If status were “hyper”, upper("hyper") would return “HYPER”.

Database limitations

Limitations are noted for each aggregation and function above, and here there are in summary:

H2: Median, Percentile and regexextract

MySQL/MariaDB: Median, Percentile.

SQL Server: Median, Percentile and regexextract

SQLite: log, Median, Percentile, power, regexextract, StandardDeviation, sqrt and Variance

Vertica: Median and Percentile

Additionally, Presto only provides approximate results for Median and Percentile.

If you’re using or maintaining a third-party database driver, please refer to the wiki to see how your driver might be impacted.

See Custom expressions in the notebook editor to learn more.