Indicators

Indicators show one value. All three read it from the last row the query returns, so a one-row query and a time series ordered oldest first both work: in a series, the last row is "now".

KPI

One number, large, with its change and its trend.

Acme's open orders, filtered by the Branch variable:

SELECT count(*) AS open_orders
FROM work_orders
WHERE status NOT IN ('COMPLETED', 'CANCELLED')
  AND ($VAR.branch IS NULL OR branch = $VAR.branch::uuid)

Revenue this month, with its trend over the year: choose month for Time, then turn on Show change and Show trend.

SELECT date_trunc('month', i.issued_on)::date AS month,
       sum(ii.quantity * ii.unit_price) AS revenue
FROM invoices i
JOIN invoice_items ii ON ii.invoice = i.id
WHERE i.status <> 'DRAFT'
  AND i.issued_on >= date_trunc('month', current_date) - interval '11 months'
GROUP BY 1
ORDER BY 1

KPI

One number, large - with its change and its trend.

Size: 3 × 2 of the 12-column grid, at least 2 × 2 · Also: thresholds, events, export

SELECT 1250 AS revenue, 1100 AS previous

Gauge

A value on an arc, from 0 to its target. The value is clamped to the arc.

The share of open orders still within their due time, with Format 0.0 %:

SELECT round(100.0 * count(*) FILTER (WHERE due_at >= now())
             / nullif(count(*), 0), 1) AS on_time
FROM work_orders
WHERE status NOT IN ('COMPLETED', 'CANCELLED')

With Thresholds red, amber from 80, green from 95, the arc is drawn in those bands.

GAUGE

A value on an arc, against its target.

Size: 4 × 3 of the 12-column grid, at least 2 × 2 · Also: thresholds, events, export

SELECT 72 AS satisfaction

Progress

A bar of a value toward its target: $ 13.9M of $ 30M · 46 %.

Billed this month against the month's goal; choose goal for Target:

SELECT sum(ii.quantity * ii.unit_price) AS billed, 30000000 AS goal
FROM invoices i
JOIN invoice_items ii ON ii.invoice = i.id
WHERE i.status <> 'DRAFT'
  AND i.issued_on >= date_trunc('month', current_date)

Past the target, the bar is full. In a card one row high, the value and the bar sit side by side.

PROGRESS

A bar of a value toward its target - "$ 13.9M of $ 30M · 46 %", colored by thresholds.

Size: 4 × 2 of the 12-column grid, at least 3 × 1 · Also: thresholds, events, export

SELECT 13900000 AS billed, 30000000 AS goal

Thresholds

KPI, Gauge and Progress take Thresholds: a base color, then each next color from its value up. A KPI's number, a Gauge's arc and a Progress bar take the color their value falls in, over Color. Each threshold's From is one of:

• a Number: 80;
• a Column of the row the widget shows, such as a target the query computes;
• a Variable of the dashboard, Number or Select, so viewers can move it.

Colors are palette names. A threshold that names a column the query doesn't return, or a variable that doesn't exist, is refused on save.

Next steps

• Comparison: bars, combos, waterfalls and rankings.
• Editing a Widget: roles and formats.