| title | redirect_from | |
|---|---|---|
List of expressions |
|
For an introduction to expressions, check out Writing expressions in the notebook editor.
- Aggregations
- Functions
- abs
- between
- case
- ceil
- coalesce
- concat
- contains
- convertTimezone
- datetimeAdd
- datetimeDiff
- datetimeSubtract
- day
- endswith
- exp
- floor
- hour
- interval
- isempty
- isnull
- lefttrim
- length
- log
- lower
- minute
- month
- now
- power
- quarter
- regexextract
- replace
- righttrim
- round
- second
- sqrt
- startswith
- substring
- trim
- upper
- week
- weekday
- year
- Database limitations
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]).
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.
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 (ceil as in ceiling).
Syntax: ceil(column).
Example: ceil([Price]). ceil(2.99) would return 3.
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.
Shifts a date or timestamp value into a specified time zone.
Syntax: convertTimezone(column, target, source).
Example: convertTimezone("2022-12-28T12:00:00", "Canada/Pacific", "Canada/Eastern") would return 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") would return 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") will return the number of days between d1 and d2.
Syntax: datetimeDiff(datetime1, datetime2, unit).
Example: datetimeDiff("2022-02-01", "2022-03-01", "month") would return 1.
Subtracts some unit of time from a date or timestamp value.
Syntax: datetimeSubtract(column, amount, unit).
Example: datetimeSubtract("2021-03-25", 1, "month") would return 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") would return 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. (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.
Takes a datetime and returns the hour as an integer (0-23).
Syntax: hour([datetime column]).
Example: hour("2021-03-25T12:52:37") would return 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]) 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".
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.
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") would return the month as an integer, 3.
Returns the current date and time using your Metabase 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.
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 mindkiller. ", the expression would return "Fear is the mindkiller."
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".
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 Metabase 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.
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")
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.
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.