Functions
Aggregate functions
Section titled “Aggregate functions”sum(), avg(), min(), and max() work on any number or money field, including custom properties, on the object or a related one:
FROM accountsSHOW avg(annual_revenue), sum(mrr), max(number_of_employees), sum(deals.amount)GROUP BY industry| Function | Result |
|---|---|
sum(field) |
The total |
avg(field) |
The average, over records that have a value |
min(field) |
The smallest value |
max(field) |
The largest value |
Use them anywhere a metric fits: in math (sum(amount) / count), HAVING, ORDER BY, and VISUALIZE. Each one is also named after its function and field, so ORDER BY avg_annual_revenue works too.
Money fields are converted to the report currency when the field has a base-currency amount or its own exchange rate (deal amounts, invoice and order totals, subscription MRR and similar). Prefer a catalog metric when one fits: won_value already knows which deals are won.
Combine metrics with +, -, *, / and parentheses, and name the result with AS:
SHOW won_value / won_count AS avg_won, won_count * 100 / count AS win_pctSee FROM and SHOW.
Date functions
Section titled “Date functions”Date functions give the start or end of a period, offset from today:
| Function | Example | Means |
|---|---|---|
startOfDay, endOfDay |
startOfDay(-30d) |
Midnight, 30 days ago |
startOfWeek, endOfWeek |
startOfWeek(-1w) |
Monday of last week |
startOfMonth, endOfMonth |
endOfMonth(-1m) |
The last day of last month |
startOfQuarter, endOfQuarter |
startOfQuarter(0q) |
The first day of this quarter |
startOfYear, endOfYear |
startOfYear(0y) |
January 1st |
The offset uses d, w, m, q, or y. Use them in SINCE and UNTIL and in date conditions:
SINCE startOfMonth(-1m) UNTIL endOfMonth(-1m) -- last monthWHERE close_date < startOfQuarter(0q)