List of expressions


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]) returns 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) returns the number of rows where the subtotal is 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 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.


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.


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.

Related: Min, Max, Average.


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.


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.


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.


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.


Adds up all the values of the column.

Syntax: Sum(column)

Example: Sum([Subtotal]) adds 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") adds 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]) returns 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 of 150 or less would return "Small".


Rounds a decimal up (ceil 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 a string2 within it.

Syntax: contains(string1, string2)

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

Related: regexextract.


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.


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.


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.


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.


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.


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.

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 returns 1.

Related: ceil, round.


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.


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]) returns true if there is no value in the discount field.


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.


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 (since "wizard" has six characters).


Returns the base 10 log of the number.

Syntax: log(column).

Example: log([Value]).


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.


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.


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.


Returns the current date and time using your project report timezone.

Syntax: now.


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.


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.


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, substring.


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").


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

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.

Example: round([Temperature] * 10) / 10. If the temp were 100.75, the expression would return 100.8.


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.


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], 1, 10) returns the first 10 letters of a string (the string index starts at position 1).

Related: regexextract, 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".


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.


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.


  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")


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.