Formula table calculations
Build table calculations using a spreadsheet-style formula syntax instead of raw SQL
Formula table calculations let you write table calculations in a spreadsheet-style syntax, the way you would in Google Sheets or Excel, instead of raw SQL. When you create a new table calculation, Formula is the default input mode. You can switch to the SQL editor any time.
Why use formulas?
- Faster to write for common calculations. No
OVER (…)orCASE WHENboilerplate to remember. - Familiar if you've used Google Sheets, Excel, or Airtable.
- Portable across warehouses. The same formula compiles to the correct SQL for whichever warehouse your project is connected to.
If you need something the formula syntax doesn't cover yet, the SQL editor is always one click away.
Supported warehouses
Formula table calculations work on every warehouse Qyra supports: Athena, BigQuery, ClickHouse, Databricks, DuckDB, PostgreSQL, Redshift, Snowflake, and Trino. The same formula compiles to the correct SQL for whichever warehouse your project is connected to.
Writing your first formula
Every formula starts with =. Reference a field by its column name
(the same name you see in the results table header):
=orders_total_order_amount * 1.2The result appears as a new green column in your results table:
You can use:
- Numbers:
42,3.14,-1.5 - Strings:
"hello"or'hello' - Booleans:
TRUE,FALSE - Column references: any field present in your results table
- Arithmetic operators:
+,-,*,/,%(modulo) - Comparison operators:
=,<>,>,<,>=,<= - Boolean operators:
AND,OR,NOT
Function reference
Math
| Function | Description |
|---|---|
ABS(x) | Absolute value |
ROUND(x, [digits]) | Round to N decimal places |
CEIL(x) / CEILING(x) | Round up to nearest integer |
FLOOR(x) | Round down to nearest integer |
MIN(x, [y]) | Minimum (scalar or aggregate) |
MAX(x, [y]) | Maximum (scalar or aggregate) |
Logical
| Function | Description |
|---|---|
IF(condition, then, [else]) | Conditional expression |
AND, OR, NOT | Boolean operators |
=, <>, >, <, >=, <= | Comparison operators |
String
| Function | Description |
|---|---|
CONCAT(a, b, …) | Concatenate strings |
LEN(s) / LENGTH(s) | String length |
TRIM(s) | Remove whitespace |
LOWER(s) | Convert to lowercase |
UPPER(s) | Convert to uppercase |
Date
| Function | Description |
|---|---|
TODAY() | Current date |
NOW() | Current timestamp |
YEAR(d) | Extract year |
MONTH(d) | Extract month |
DAY(d) | Extract day |
LAST_DAY(d) | Last day of the month containing d |
DATE_TRUNC(d, unit) | Truncate d to the start of unit ("day", "week", "month", "quarter", "year") |
DATE_ADD(d, n, unit) | Add n whole units to d (e.g. DATE_ADD(orders_date, 3, "month")) |
DATE_SUB(d, n, unit) | Subtract n whole units from d |
DATE_DIFF(start, end, unit) | Whole-unit calendar-boundary difference. Positive when end > start. |
unit is always one of "day", "week", "month", "quarter",
"year" — quoted as a string literal.
Aggregation
| Function | Description |
|---|---|
SUM(x) | Sum values |
AVG(x) / AVERAGE(x) | Average values |
COUNT([x]) | Count rows (or non-null x) |
SUMIF(condition, x) | Sum where condition is true |
AVERAGEIF(condition, x) | Avg where condition is true |
COUNTIF(condition) | Count where condition true |
Window
| Function | Description |
|---|---|
RUNNING_TOTAL(x) | Running (cumulative) total |
ROW_NUMBER() | Sequential row number |
RANK() | Rank with gaps |
DENSE_RANK() | Rank without gaps |
LAG(x, [offset], [default]) | Previous row's value |
LEAD(x, [offset], [default]) | Next row's value |
FIRST(x) | First value in window |
LAST(x) | Last value in window |
NTILE(n) | Distribute rows into N buckets |
MOVING_SUM(x, n) | Sum of the last N rows |
MOVING_AVG(x, n) | Average of the last N rows |
Null handling
| Function | Description |
|---|---|
COALESCE(a, b, …) | First non-null argument |
ISNULL(x) | TRUE if x is null |
Window clauses: PARTITION BY and ORDER BY
Window functions accept optional PARTITION BY and ORDER BY clauses,
written inline as extra arguments. Use them when you want a running
total, moving average, rank, or LAG/LEAD to reset for each group or
follow a specific row order.
PARTITION BY <dimension>— restart the calculation for each value of the dimension (for example, run a separate running total per region).ORDER BY <dimension>— define the row order the window uses (for example, by date). Defaults to the natural sort of your results table when omitted.
Running total per group
=RUNNING_TOTAL(orders_revenue, PARTITION BY orders_region, ORDER BY orders_order_date)Moving average per group
=MOVING_AVG(orders_revenue, 6, PARTITION BY orders_partner_name, ORDER BY orders_order_month)Percent change vs. previous row in the same group
=(orders_revenue - LAG(orders_revenue, PARTITION BY orders_region, ORDER BY orders_order_date))
/ LAG(orders_revenue, PARTITION BY orders_region, ORDER BY orders_order_date)Rank within each group
=RANK(PARTITION BY orders_region, ORDER BY orders_revenue DESC)You can also wrap any aggregate with an explicit OVER (…) clause for
warehouse-style window aggregates:
=SUM(orders_revenue) OVER (PARTITION BY orders_region ORDER BY orders_order_date)Examples
Gross margin as a percentage
=ROUND((orders_revenue - orders_cost) / orders_revenue * 100, 2)Flag high-value orders
=IF(orders_total_amount > 1000, "VIP", "Standard")Running total of revenue
=RUNNING_TOTAL(orders_revenue)Period-over-period growth %
=(orders_revenue - LAG(orders_revenue, 1, 0)) / LAG(orders_revenue, 1, 0) * 100Bucket customers by spend
=IF(customers_lifetime_value > 10000, "Platinum",
IF(customers_lifetime_value > 5000, "Gold",
IF(customers_lifetime_value > 1000, "Silver", "Bronze")))Percent of total
=orders_revenue / SUM(orders_revenue) * 100