Functions Reference

When building views with computed columns, you can use these helper functions alongside standard SQL. They handle common data cleanup patterns that come up constantly when working with government and public datasets.

How to use them

Call functions in any expression field using function_name(args) syntax. Reference dataset columns with {column_name}:

{
  "computed": [
    {
      "name": "date",
      "sql": "make_date({year}, period_to_month({period}), 1)"
    }
  ]
}

Functions nest and compose freely. You can mix them with standard SQL:

{
  "sql": "ROUND(safe_divide({revenue}, {cost}, 0) * 100, 2)"
}

Date functions

Function	Description	Example
`make_date(year, month, day)`	Build a date from year, month, and day parts	`make_date({year}, {month}, 1)`
`period_to_month(period)`	Convert BLS period codes (M01-M12, Q01-Q04, S01-S02) to month numbers	`period_to_month({period})`
`quarter_start_month(quarter)`	Quarter number to its first month (1→1, 2→4, 3→7, 4→10)	`quarter_start_month({quarter})`

Date example

BLS data stores time as a year and a period code like M03 (March) or Q02 (second quarter). To get a proper date:

{
  "name": "date",
  "sql": "make_date({year}, period_to_month({period}), 1)"
}
// M03 + 2025 → 2025-03-01
// Q02 + 2025 → 2025-04-01

Math functions

Function	Description	Example
`safe_divide(num, denom, default?)`	Division that returns null (or a default) instead of erroring on zero/null denominators	`safe_divide({revenue}, {cost}, 0)`
`pct_change(current, previous)`	Percentage change between two values. Returns null if previous is zero	`pct_change({value_2024}, {value_2023})`
`per_capita(value, population, scale?)`	Per-capita calculation with optional scaling (e.g., per 100k people)	`per_capita({cases}, {population}, 100000)`

Math examples

Safe division (won't error on zero)

// revenue=100, cost=50 → 2.0
// revenue=100, cost=0  → 0 (uses default)
// revenue=100, cost=null → 0
{"sql": "safe_divide({revenue}, {cost}, 0)"}

Year-over-year percentage change

// value_2024=105, value_2023=100 → 5.0
// value_2024=100, value_2023=0   → null
{"sql": "pct_change({value_2024}, {value_2023})"}

Cases per 100,000 people

// cases=1500, population=500000 → 300.0
{"sql": "per_capita({cases}, {population}, 100000)"}

String functions

Function	Description	Example
`pad_left(value, length, char)`	Left-pad a string to a given length (e.g., zero-pad FIPS codes)	`pad_left({code}, 5, '0')`
`extract_digits(value)`	Strip everything except digits from a string	`extract_digits({phone})`

String examples

Zero-pad FIPS codes

// code="123" → "00123"
// code="45"  → "00045"
{"sql": "pad_left({fips_code}, 5, '0')"}

Clean phone numbers

// "(555) 123-4567" → "5551234567"
{"sql": "extract_digits({phone})"}

Null handling

Government data loves sentinel values. Missing data might be *, -999, 0, or just blank. These functions clean that up:

Function	Description	Example
`null_if(value, sentinel)`	Convert a specific value to null (clean sentinel values)	`null_if({value}, '*')`
`null_if_zero(value)`	Convert zero to null	`null_if_zero({count})`
`default_if_null(value, default)`	Replace null with a default value (like SQL's COALESCE)	`default_if_null({value}, 0)`

Null handling example

The Illinois Report Card uses * to suppress small population values for privacy. Here's how views clean that up:

Clean suppressed values and cast to number

// "*"  → null (suppressed)
// "97" → 97.0
{"sql": "TRY_CAST(null_if({student_enrollment}, '*') AS INTEGER)"}

Chain null handling with math

// Clean the value, compute percentage change, default to 0 if null
{"sql": "default_if_null(pct_change(null_if({current}, '*'), null_if({previous}, '*')), 0)"}

Standard SQL functions

You can also use standard SQL functions alongside the helpers above. These are the most commonly used:

Category	Functions
Math	`ABS`, `CEIL`, `FLOOR`, `ROUND`, `SQRT`, `POWER`, `LN`, `LOG`
String	`CONCAT`, `SUBSTRING`, `LENGTH`, `UPPER`, `LOWER`, `TRIM`, `REPLACE`, `SPLIT_PART`
Date	`DATE_TRUNC`, `DATE_PART`, `EXTRACT`, `MAKE_DATE`, `AGE`
Null	`COALESCE`, `NULLIF`
Aggregate	`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `ARRAY_AGG`, `STRING_AGG`
Window	`ROW_NUMBER`, `RANK`, `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`
Conditional	`CASE WHEN ... THEN ... ELSE ... END`
Cast	`CAST(x AS type)`, `TRY_CAST(x AS type)`

Use TRY_CAST instead of CAST when values might not be convertible (e.g., columns that mix numbers with suppression markers like * or X). TRY_CAST returns null instead of erroring.

Views Guide - Creating and using views
Creating Views - Build views in the workbench or via API
Querying Guide - Filtering, sorting, and pagination