Syntax: datetimeDiff(datetime1, datetime2, unit).Įxample: datetimeDiff("", "", "month") would return 1. For example, datetimeDiff(d1, d2, "day") will return the number of days between d1 and d2. Returns the difference between two datetimes in some unit of time. Syntax: datetimeAdd(column, amount, unit).Įxample: datetimeAdd("", 1, "month") would return the value, displayed as April 25, 2021. datetimeAddĪdds some unit of time to a date or timestamp value. Syntax: convertTimezone(column, target, source).Įxample: convertTimezone("T12:00:00", "Canada/Pacific", "Canada/Eastern") would return the value T09:00:00, displayed as December 28, 2022, 9:00 AM. Shifts a date or timestamp value into a specified time zone. If Status were “Classified”, the expression would return true. containsĬhecks to see if string1 contains string2 within it.Įxample: contains(, "Class"). concatĮxample: concat(, ", ", ) would produce a string of the format “Last Name, First Name”, like “Palazzo, Enrico”. If both the Comments and Notes columns are null for that row, the expression will return the string “No comments”. Looks at the values in each argument in order and returns the first non-null value for each row.Įxample: coalesce(,, "No comments"). Rounds a decimal up (ceil as in ceiling).Įxample: ceil(). In this case, the default value is “Small”, so any Weight 150 or less would return “Small”. 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.Įxample: case( > 200, "Large", > 150, "Medium", "Small") If a Weight is 250, the expression would return “Large”. betweenĬhecks a date or number column’s values to see if they’re within the specified range.Įxample: between(, "", "") would return rows where Created At date fell within the range of Januand December 31, 2020. Returns the absolute (positive) value of the specified column.Įxample: abs(). They can be used to alter or filter values in a column, or create new, custom columns. Functionsįunction expressions apply to each individual value. Returns the numeric variance for a given column.Įxample: Variance() will return a measure of the dispersion from the mean temperature for all temps in that column. Sums up the specified column only for rows where the condition is true.Įxample: SumIf(, = "Valid") would add up all the subtotals for orders with a status of “Valid”. SumĮxample: Sum() would add up all the values in the Subtotal column. Low standard deviation indicates values cluster around the mean, whereas a high standard deviation means the values are spread out over a wide range.Įxample: StandardDeviation() would return the SD for the values in the Population column. StandardDeviationĬalculates the standard deviation of the column, which is a measure of the variation in a set of values. Returns the percent of rows in the data that match the condition, as a decimal.Įxample: Share( = "Blue") would return the number of rows with the Color field set to Blue, divided by the total number of rows. Presto only provides approximate results. Syntax: Percentile(column, percentile-value)Įxample: Percentile(, 0.9) would return the value at the 90th percentile for all values in that column.ĭatabases that don’t support percentile: H2, MySQL, SQL Server, SQLite, Vertica. Returns the value of the column at the percentile value. Returns the smallest value found in the column.Įxample: Min() would find the lowest salary among all salaries in the Salary column. Returns the median value of the specified column.Įxample: Median() would find the midpoint age where half of the ages are older, and half of the ages are younger.ĭatabases that don’t support median: SQLite, Vertica, SQL server, MySQL. Returns the largest value found in the column.Įxample: Max() would return the oldest age found across all values in the Age column. Duplicates (of the last name “Smith” for example) are not counted. Returns the count of unique last names in the column. The number of distinct values in this column.ĭistinct(). The rolling sum of a column across a breakout. The additive total of rows across a breakout. Only counts rows where the condition is true.Įxample: CountIf( > 100) would return the number of rows where the subtotal were greater than 100. Returns the count of rows (also known as records) in the selected data.Įxample: Count If a table or result returns 10 rows, Count will return 10. Returns the average of the values in the column.Įxample: Average() would return the mean for the Quantity field. They can only be used in the Summarize section of the notebook editor. For an introduction to expressions, check out Writing expressions in the notebook editor.Īggregation expressions take into account all values in a field.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |