Looking to up your game in Creator with more sophisticated analysis? Here's a list of things to try
Custom expressions
Custom expressions are like formulas in spreadsheet software like Excel, Google Sheets, and LibreOffice Calc. They are the power tools in the notebook editor of the query builder that allow you to ask more complicated questions.
List of expressions
Aggregations
Aggregation expressions take into account all values in a field. They can only be used in the Summarise section of the query builder.
Average
Returns the average of the values in the column.
Syntax: Average(column)
Example: Average([Quantity]) would return the mean for the Quantity field.
Count
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.
CountIf
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.
Distinct
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.
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: MariaDB, MySQL, SQLite, Vertica, and SQL Server. Presto only provides approximate results.
Min
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.
Percentile
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, MariaDB, MySQL, SQL Server, SQLite, Vertica. Presto only provides approximate results.
Share
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.
StandardDeviation
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.
Sum
Adds up all the values of the column.
Syntax: Sum(column)
Example: Sum([Subtotal]) would add up all the values in the Subtotal column.
SumIf
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”.
Variance
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.
Functions
Function expressions apply to each individual value. They can be used to alter or filter values in a column, or create new, custom columns.
Logical functions
Logical functions determine if a condition is satisfied or determine what value to return based on a condition.
between
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.
case
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”.
coalesce
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”.
isnull
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.
notnull
Returns true if the column contains a value.
Syntax: notnull(column)
Example: notnull([Tax]) would return true if there is a value present in the column for that row.
Math functions
Math functions implement common mathematical operations.
abs
Returns the absolute (positive) value of the specified column.
Syntax: abs(column)
Example: abs([Debt]). If Debt were -100, abs(-100) would return 100.
ceil
Rounds a decimal up (ceil as in ceiling).
Syntax: ceil(column).
Example: ceil([Price]). ceil(2.99) would return 3.
exp
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])
floor
Rounds a decimal number down.
Syntax: floor(column)
Example: floor([Price]). If the Price were 1.99, the expression would return 1.
Returns the base 10 log of the number.
Syntax: log(column).
Example: log([Value]).
power
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.
round
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.
sqrt
Returns the square root of a value.
Syntax: sqrt(column).
Example: sqrt([Hypotenuse]).
Databases that don’t support sqrt: SQLite.