Custom expressions are a way to create more advanced filters and aggregations, or to add custom columns to your custom question. These expressions are accessible in the notebook editor of custom questions when clicking the button to add a new filter, a new metric in the Summarize area, or when creating a new custom column.
In each of these three places, you can:
/(divide) on numeric column with numeric values, like integers, floats, and doubles. You can’t currently do math on timestamp columns.
>=(greater than or equal to),
<=(less than or equal to),
!=(not equal to).
[Name of Column]. Columns in connected tables can be referred to like this:
[Valid User Sessions].
Some of the functions listed below can only be used inside of a metric expression in the Summarize area, because they aggregate an entire column. So while you could create a custom column with the formula
[Subtotal] + [Tax], you could not write
Sum([Subtotal] + [Tax]) unless you were creating a custom metric expression. Here are the functions that can only be used when writing a metric expression:
Some other things to keep in mind about filter expressions and conditionals:
[Subtotal] + [Tax] < 100, but not just
[Subtotal] + [Tax].
sumifaggregations, like so:
countif( round([Subtotal]) > 100 OR floor([Tax]) < 10 )
If you want to work with dates in your filter expressions, they’ll need to follow the format,
"YYYY-MM-DD" — i.e., four characters for the year, two for the month, and two for the day, enclosed in quotes and separated by dashes.
between([Created At], "2020-01-01", "2020-03-31") OR [Received At] > "2019-12-25"
This would return rows where
Created At is between January 1, 2020 and March 31, 2020, or where
Received At is after December 25, 2019.
||Returns the absolute (positive) value of the specified column.||
||Returns the average of the values in the column.||
||Checks a date or number column’s values to see if they’re within the specified range.||
||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.||
||Rounds a decimal number up.||
||Looks at the values in each argument in order and returns the first non-null value for each row.||
||Combine two or more strings of text together.||
||Checks to see if string1 contains string2 within it.||
||Returns the count of rows in the selected data.||
||Only counts rows where the condition is true.||
||The additive total of rows across a breakout.||
||The rolling sum of a column across a breakout.||
||The number of distinct values in this column.||
||Returns true if the end of the text matches the comparison text.||
||Returns Euler’s number, e, raised to the power of the supplied number.||
||Rounds a decimal number down.||
||Checks a date column’s values to see if they’re within the relative range.||
||Removes leading whitespace from a string of text.||
||Returns the number of characters in text.||
||Returns the base 10 log of the number.||
||Returns the string of text in all lower case.||
||Returns the largest value found in the column.||
||Returns the median value of the specified column.||
||Returns the smallest value found in the column||
||Returns the value of the column at the percentile value.||
||Raises a number to the power of the exponent value.||
||Extracts matching substrings according to a regular expression.||
||Replaces a part of the input text with new text.||
||Removes trailing whitespace from a string of text.||
||Rounds a decimal number either up or down to the nearest integer value.||
||Returns the percent of rows in the data that match the condition, as a decimal.||
||Returns the square root.||
||Calculates the standard deviation of the column.||
||Returns true if the beginning of the text matches the comparison text.||
||Returns a portion of the supplied text.||
||Adds up all the values of the column.||
||Sums up the specified column only for rows where the condition is true.||
||Removes leading and trailing whitespace from a string of text.||
||Returns the text in all upper case.||
||Returns the numeric variance for a given column.||
Certain database types don’t support some of the above functions:
Additionally, Presto only provides approximate results for
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.