Expressions and functions

The formula language of filter, formula, aggregate and holdout.

Expressions are written as YAML values:

filter: heat > threshold and not contains(name, 'Kentron')
formula:
  heat_index: round(zscore(lst_c, year), 2)

Values and names

Numbers: 42, 0.5, 1e6. Text: 'Yerevan' or "Yerevan". true, false, null.
A bare name is a column of the table or a parameter.

From the loosest binding to the tightest:

Operators	Meaning
`or`, `\|\|`	either
`and`, `&&`	both
`==`, `!=`, `<`, `<=`, `>`, `>=`	comparison
`+`, `-`	addition, subtraction
`*`, `/`, `%`	multiplication, division, remainder
`^`	power
`not`, `!`, unary `-`	negation

Parentheses group. An operation on an empty value (null) gives null.

Computed on each row.

Function	Result
`abs(x)`, `sqrt(x)`, `exp(x)`, `log(x)` / `ln(x)`	arithmetic
`floor(x)`, `ceil(x)`, `round(x, digits)`	rounding (`digits` defaults to 0)
`pow(x, y)`	x to the power y
`min(a, b, …)`, `max(a, b, …)`	smallest, largest of the arguments
`if(condition, then, else)`	a choice
`coalesce(a, b, …)`	the first argument that is not empty
`is_null(x)`	whether x is empty
`num(x)`, `str(x)`	conversion
`concat(a, b, …)`, `lower(t)`, `upper(t)`, `contains(t, part)`	text
`year(t)`, `month(t)`, `day(t)`	parts of a date
`before(t, "YYYY-MM-DD")`, `after(t, "YYYY-MM-DD")`	date comparison

Computed over the whole column, or per group: trailing names after the value arguments are the grouping columns. The rows keep their order.

Function	Result
`zscore(x, by…)`	standard score
`rank_pct(x, by…)`	percentile rank, 0–1
`lag(x, n, by…)`, `lead(x, n, by…)`	the value `n` rows earlier, later
`diff(x, by…)`	change from the previous row
`pct_change(x, by…)`	change from the previous row, in %
`cumsum(x, by…)`	running total
`mean_by(x, by…)`, `median_by`, `sum_by`, `min_by`, `max_by`	the group’s value, on every row
`count_by(by…)`	the group’s number of rows
`quantile_by(x, q, by…)`	the group’s `q` quantile
`chain(base, change_pct, base_year)`	a level series from year-on-year % changes: `base` in `base_year`, compounded forwards and backwards (needs a `year` column)

formula:
  heat_z: zscore(lst_c, year)          # relative to each year's cells
  growth: pct_change(wage, series)     # per series

Used in aggregate, one value per group.

Function	Result
`count()`, `count(x)`	rows; rows where x is not empty
`sum(x)`, `mean(x)`, `median(x)`, `std(x)`	the usual summaries
`min(x)`, `max(x)`	smallest, largest
`first(x)`, `last(x)`	the first, last value in row order
`quantile(x, q)`	the `q` quantile, e.g. `quantile(x, 0.8)`
`share(condition)`	the share of rows the condition is true for

An aggregate function’s argument can be an expression: mean(lst_c - lst_mean).