Custom chart examples¶
Eight custom chart templates, each a shape people ask for that
dbt Charts has no built-in type: for. Copy a template into your project's
templates/ folder, then point a chart at it with template:.
Every template here reads its colors and fonts from style, so each one
follows the board's theme. The Playground's
custom charts
example puts all eight on one board.
Sankey¶
Best for: flows between stages, where one stage splits into several and several merge into one: visitors to plans, budget sources to uses.
The query is an edge list, one row per flow. Nodes sit in columns by their longest path from a starting node, and every node with no outgoing flow ends in the last column. Flows that loop back stop the render with an error.
charts:
signup_sankey:
type: custom
template: sankey
title: Where visitors go
query:
columns: [source, target, visitors]
values:
- [Visits, Signup, 4200]
- [Visits, Bounced, 9800]
- [Signup, Trial, 3100]
- [Signup, Abandoned, 1100]
- [Trial, Paid, 1300]
- [Trial, Churned, 1800]
- [Paid, Annual, 500]
- [Paid, Monthly, 800]
x: [source, target]
value: visitors
rows:
- signup_sankey
templates/sankey.svg.j2
{#---
description: Flow diagram from an edge list, one row per flow. Nodes sit in columns by their longest path from a start node; nodes with no outgoing flow end in the last column.
channels:
x:
description: Two columns, the node each flow leaves and then the node it enters.
multiple: true
value:
description: Column holding the size of each flow.
options:
value_format:
type: string
default: ',.0f'
description: d3-format spec for node values in labels.
node_width:
type: number
default: 10
node_gap:
type: number
default: 12
---#}
{% set p = options %}
{% if channels.x | length != 2 %}{{ raise_error("x takes two columns: the source node, then the target node") }}{% endif %}
{% set source_col = channels.x[0] %}
{% set target_col = channels.x[1] %}
{% set value_col = channels.value %}
{% set label_size = style.font.size %}
{% set ns = namespace(names=[], out={}, inn={}, depth={}, y={}, k=none, so={}, to={}) %}
{# Collect nodes in query order and their in/out totals. #}
{% for r in rows %}
{% set v = r[value_col] | float %}
{% if v <= 0 %}{{ raise_error("Flow values must be positive: " ~ r[source_col] ~ " -> " ~ r[target_col] ~ " is " ~ v) }}{% endif %}
{% for n in [r[source_col] | string, r[target_col] | string] %}
{% if n not in ns.names %}{% set ns.names = ns.names + [n] %}{% endif %}
{% endfor %}
{% set ns.out = dict(ns.out, **{r[source_col] | string: ns.out.get(r[source_col] | string, 0) + v}) %}
{% set ns.inn = dict(ns.inn, **{r[target_col] | string: ns.inn.get(r[target_col] | string, 0) + v}) %}
{% endfor %}
{# Column = longest path from a start node. #}
{% for n in ns.names %}{% set ns.depth = dict(ns.depth, **{n: 0}) %}{% endfor %}
{% for _ in ns.names %}
{% for r in rows %}
{% set s = r[source_col] | string %}{% set t = r[target_col] | string %}
{% if ns.depth[t] <= ns.depth[s] %}{% set ns.depth = dict(ns.depth, **{t: ns.depth[s] + 1}) %}{% endif %}
{% endfor %}
{% endfor %}
{% for r in rows %}
{% if ns.depth[r[target_col] | string] <= ns.depth[r[source_col] | string] %}
{{ raise_error("Flows form a cycle through " ~ r[source_col] ~ " -> " ~ r[target_col] ~ "; a Sankey needs flows that only move forward.") }}
{% endif %}
{% endfor %}
{% set last = ns.depth.values() | max %}
{% for n in ns.names if n not in ns.out %}{% set ns.depth = dict(ns.depth, **{n: last}) %}{% endfor %}
{% set ns.k = 1e12 %}
{% for c in range(last + 1) %}
{% set total = namespace(v=0, n=0) %}
{% for n in ns.names if ns.depth[n] == c %}
{% set total.v = total.v + [ns.out.get(n, 0), ns.inn.get(n, 0)] | max %}{% set total.n = total.n + 1 %}
{% endfor %}
{% set ns.k = [ns.k, (height - p.node_gap * (total.n - 1)) / total.v] | min %}
{% endfor %}
{% set k = ns.k %}
{% set colw = (width - p.node_width) / ([last, 1] | max) %}
{# Lay columns out left to right; order each column by where its inflows come from. #}
{% for c in range(last + 1) %}
{% set ns2 = namespace(items=[], total=0) %}
{% for n in ns.names if ns.depth[n] == c %}
{% set nv = [ns.out.get(n, 0), ns.inn.get(n, 0)] | max %}
{% set bc = namespace(w=0, v=0) %}
{% for r in rows if (r[target_col] | string) == n and (r[source_col] | string) in ns.y %}
{% set bc.w = bc.w + (ns.y[r[source_col] | string] | float) * (r[value_col] | float) %}{% set bc.v = bc.v + (r[value_col] | float) %}
{% endfor %}
{% set ns2.items = ns2.items + [{"name": n, "value": nv, "order": (bc.w / bc.v) if bc.v else loop.index}] %}
{% set ns2.total = ns2.total + nv * k %}
{% endfor %}
{% set cy = namespace(y=(height - ns2.total - p.node_gap * ((ns2.items | length) - 1)) / 2) %}
{% for it in ns2.items | sort(attribute="order") %}
{% set ns.y = dict(ns.y, **{it.name: cy.y}) %}
{% set cy.y = cy.y + it.value * k + p.node_gap %}
{% endfor %}
{% endfor %}
{# Stack each node's links in the order of the nodes at their other end. #}
{% set ns.links = [] %}
{% for r in rows %}
{% set s = r[source_col] | string %}{% set t = r[target_col] | string %}
{% set ns.links = ns.links + [{"i": "l" ~ loop.index, "s": s, "t": t, "v": r[value_col] | float, "spos": ns.y[s], "tpos": ns.y[t]}] %}
{% endfor %}
{% for n in ns.names %}
{% set off = namespace(o=ns.y[n], i=ns.y[n]) %}
{% for l in ns.links | selectattr("s", "equalto", n) | sort(attribute="tpos") %}
{% set ns.so = dict(ns.so, **{l.i: off.o}) %}{% set off.o = off.o + l.v * k %}
{% endfor %}
{% for l in ns.links | selectattr("t", "equalto", n) | sort(attribute="spos") %}
{% set ns.to = dict(ns.to, **{l.i: off.i}) %}{% set off.i = off.i + l.v * k %}
{% endfor %}
{% endfor %}
<g fill-opacity="0.35">
{% for l in ns.links %}
{% set x0 = ns.depth[l.s] * colw + p.node_width %}{% set x1 = ns.depth[l.t] * colw %}{% set xm = (x0 + x1) / 2 %}
{% set s0 = ns.so[l.i] %}{% set t0 = ns.to[l.i] %}{% set th = l.v * k %}
<path fill="{{ color_for(l.s, source_col) }}" d="M{{ x0|round(1) }},{{ s0|round(1) }}C{{ xm|round(1) }},{{ s0|round(1) }} {{ xm|round(1) }},{{ t0|round(1) }} {{ x1|round(1) }},{{ t0|round(1) }}V{{ (t0 + th)|round(1) }}C{{ xm|round(1) }},{{ (t0 + th)|round(1) }} {{ xm|round(1) }},{{ (s0 + th)|round(1) }} {{ x0|round(1) }},{{ (s0 + th)|round(1) }}Z"><title>{{ l.s }} → {{ l.t }}: {{ l.v | format(p.value_format) }}</title></path>
{% endfor %}
</g>
<g font-family="{{ style.font.family }}" font-size="{{ label_size }}" fill="{{ style.font.color }}">
{% for n in ns.names %}
{% set nv = [ns.out.get(n, 0), ns.inn.get(n, 0)] | max %}
{% set x = ns.depth[n] * colw %}{% set y = ns.y[n] %}{% set hgt = [nv * k, 1] | max %}
<rect x="{{ x|round(1) }}" y="{{ y|round(1) }}" width="{{ p.node_width }}" height="{{ hgt|round(1) }}" fill="{{ color_for(n, source_col) }}"><title>{{ n }}: {{ nv | format(p.value_format) }}</title></rect>
{% set end = ns.depth[n] == last %}
<text x="{{ (x - 6 if end else x + p.node_width + 6)|round(1) }}" y="{{ (y + hgt / 2)|round(1) }}" dy="0.35em" text-anchor="{{ 'end' if end else 'start' }}" font-weight="600">{{ n }}<tspan dx="4" font-weight="400" fill="{{ style.muted }}">{{ nv | format(p.value_format) }}</tspan></text>
{% endfor %}
</g>
This template lays the diagram out itself, which is about as much logic as a template should hold. It does not reorder nodes to reduce crossings.
Funnel¶
Best for: how many reach each step of a process, and where they drop off.
One row per stage, in order. Bars are centered and sized by count; the band between two bars carries the share that made it to the next stage.
charts:
activation:
type: custom
template: funnel
title: Activation funnel
query:
columns: [stage, users]
values:
- [Visited site, 48210]
- [Started signup, 12940]
- [Verified email, 9120]
- [Created a board, 5310]
- [Invited a teammate, 2180]
- [Upgraded, 740]
x: stage
y: users
options:
label_width: 140
rows:
- activation
templates/funnel.svg.j2
{#---
description: Funnel of ordered stages, one row per stage in query order. Bars are centered and sized by value; the band between two bars shows the share that made it to the next stage.
channels:
x:
description: Column naming each stage.
y:
description: Column holding the count that reached each stage.
options:
value_format:
type: string
default: ',.0f'
label_width:
type: number
default: 120
description: Width reserved on the left for stage names.
---#}
{% set p = options %}
{% set stage_col = channels.x %}
{% set value_col = channels.y %}
{% if not rows %}{{ raise_error("A funnel needs at least one stage.") }}{% endif %}
{% set top = rows[0][value_col] | float %}
{% set peak = rows | map(attribute=value_col) | map("float") | max %}
{% set n = rows | length %}
{% set band = height / n %}
{% set bar_h = band * 0.62 %}
{% set plot_w = width - p.label_width - 90 %}
{% set cx = p.label_width + plot_w / 2 %}
{% set fill = style.charts.color.categorical.single_series_palette[0] %}
{% set axis_font = style.charts.axis.labels.font %}
<g font-family="{{ style.font.family }}" font-size="{{ style.font.size }}">
{% for r in rows %}
{% set v = r[value_col] | float %}
{% set w = plot_w * v / peak %}
{% set y = loop.index0 * band + (band - bar_h) / 2 %}
{% if not loop.first %}
{% set pv = rows[loop.index0 - 1][value_col] | float %}
{% set pw = plot_w * pv / peak %}
{% set py = y - band + bar_h %}
<path d="M{{ (cx - pw / 2)|round(1) }},{{ py|round(1) }}H{{ (cx + pw / 2)|round(1) }}L{{ (cx + w / 2)|round(1) }},{{ y|round(1) }}H{{ (cx - w / 2)|round(1) }}Z" fill="{{ fill }}" fill-opacity="0.15"/>
<text x="{{ width }}" y="{{ (py + (y - py) / 2)|round(1) }}" dy="0.35em" text-anchor="end" fill="{{ style.muted }}" font-size="{{ axis_font.size }}">{{ (v / pv) | format(".0%") }} of previous</text>
{% endif %}
<rect x="{{ (cx - w / 2)|round(1) }}" y="{{ y|round(1) }}" width="{{ [w, 1]|max|round(1) }}" height="{{ bar_h|round(1) }}" rx="2" fill="{{ fill }}"><title>{{ r[stage_col] }}: {{ v | format(p.value_format) }} ({{ (v / top) | format(".1%") }} of {{ rows[0][stage_col] }})</title></rect>
<text x="{{ p.label_width - 12 }}" y="{{ (y + bar_h / 2)|round(1) }}" dy="0.35em" text-anchor="end" fill="{{ style.font.color }}">{{ r[stage_col] }}</text>
<text x="{{ (cx + w / 2 + 8)|round(1) }}" y="{{ (y + bar_h / 2)|round(1) }}" dy="0.35em" font-weight="600" fill="{{ style.font.color }}">{{ v | format(p.value_format) }}</text>
{% endfor %}
</g>
Gauge¶
Best for: one number against a known range, such as a score or a utilization rate.
The query returns one row. bands splits the track into zones colored with
the theme's negative, warning, and positive tones, and the value's arc takes
the color of the zone it ends in. target draws a tick.
charts:
nps_gauge:
type: custom
template: gauge
title: Net promoter score
query:
columns: [nps, goal]
values:
- [72.4, 80]
style:
aspect_ratio: 1.8
value: nps
options:
max: 100
target: goal
bands: "50,75"
label: Target 80
value_format: ".0f"
rows:
- nps_gauge
templates/gauge.svg.j2
{#---
description: Half-circle gauge for one value against a range, from a one-row query. An optional target draws a tick; optional bands shade the track.
channels:
value:
description: Column holding the value to show.
options:
min:
type: number
default: 0
max:
type: number
description: Value at the right end of the arc.
target:
type: column
default: null
description: Optional column holding a target, drawn as a tick across the arc.
bands:
type: string
default: null
description: Optional comma-separated thresholds that split the track into negative, warning, and positive zones, for example "60,85".
value_format:
type: string
default: ',.0f'
label:
type: string
default: null
description: Optional caption under the value.
---#}
{% set p = options %}
{% set value_col = channels.value %}
{% if rows | length != 1 %}{{ raise_error("A gauge needs a one-row query; got " ~ (rows | length) ~ " rows.") }}{% endif %}
{% set row = rows[0] %}
{% set v = row[value_col] | float %}
{% set span = p.max - p.min %}
{% if span <= 0 %}{{ raise_error("max must be greater than min.") }}{% endif %}
{% set r = [width / 2, height - 30] | min - 4 %}
{% set ri = r * 0.74 %}
{% set cx = width / 2 %}
{% set cy = r + 4 %}
{# Fraction 0..1 of the range runs from the left end of the arc (pi) to the right (0). #}
{% macro band(f0, f1, color, opacity=1) %}
{% set a0 = math.pi * (1 - f0) %}{% set a1 = math.pi * (1 - f1) %}
<path d="M{{ (cx + r * math.cos(a0))|round(2) }},{{ (cy - r * math.sin(a0))|round(2) }}A{{ r|round(2) }},{{ r|round(2) }} 0 0 1 {{ (cx + r * math.cos(a1))|round(2) }},{{ (cy - r * math.sin(a1))|round(2) }}L{{ (cx + ri * math.cos(a1))|round(2) }},{{ (cy - ri * math.sin(a1))|round(2) }}A{{ ri|round(2) }},{{ ri|round(2) }} 0 0 0 {{ (cx + ri * math.cos(a0))|round(2) }},{{ (cy - ri * math.sin(a0))|round(2) }}Z" fill="{{ color }}" fill-opacity="{{ opacity }}"/>
{% endmacro %}
{% set frac = [[(v - p.min) / span, 0] | max, 1] | min %}
<g font-family="{{ style.font.family }}">
{% if p.bands %}
{% set cuts = p.bands.split(",") | map("float") | list %}
{% set edges = [p.min] + cuts + [p.max] %}
{% set zone = [style.tones.negative, style.tones.warning, style.tones.positive] %}
{% for i in range(edges | length - 1) %}
{{ band((edges[i] - p.min) / span, (edges[i + 1] - p.min) / span, zone[[i, 2] | min], 0.18) }}
{% endfor %}
{{ band(0, frac, zone[[cuts | select("le", v) | list | length, 2] | min]) }}
{% else %}
{{ band(0, 1, style.charts.axis.grid.color) }}
{{ band(0, frac, style.charts.color.categorical.single_series_palette[0]) }}
{% endif %}
{% if p.target %}
{% set a = math.pi * (1 - [[((row[p.target] | float) - p.min) / span, 0] | max, 1] | min) %}
<line x1="{{ (cx + (r + 5) * math.cos(a))|round(2) }}" y1="{{ (cy - (r + 5) * math.sin(a))|round(2) }}" x2="{{ (cx + (ri - 5) * math.cos(a))|round(2) }}" y2="{{ (cy - (ri - 5) * math.sin(a))|round(2) }}" stroke="{{ style.font.color }}" stroke-width="2.5"><title>Target: {{ row[p.target] | format(p.value_format) }}</title></line>
{% endif %}
<text x="{{ cx }}" y="{{ (cy - 4)|round(1) }}" text-anchor="middle" font-size="{{ (r * 0.34)|round(1) }}" font-weight="600" fill="{{ style.font.color }}">{{ v | format(p.value_format) }}<title>{{ p.label or value_col }}: {{ v | format(p.value_format) }}</title></text>
{% if p.label %}
<text x="{{ cx }}" y="{{ (cy + 20)|round(1) }}" text-anchor="middle" font-size="{{ style.font.size }}" fill="{{ style.muted }}">{{ p.label }}</text>
{% endif %}
<text x="{{ (cx - (r + ri) / 2)|round(1) }}" y="{{ (cy + 16)|round(1) }}" text-anchor="middle" font-size="{{ style.charts.axis.labels.font.size }}" fill="{{ style.muted }}">{{ p.min | format(p.value_format) }}</text>
<text x="{{ (cx + (r + ri) / 2)|round(1) }}" y="{{ (cy + 16)|round(1) }}" text-anchor="middle" font-size="{{ style.charts.axis.labels.font.size }}" fill="{{ style.muted }}">{{ p.max | format(p.value_format) }}</text>
</g>
Radial bar¶
Best for: a short ranked list shown compactly, where the shape matters more than reading exact values.
One ring per row, outermost first, so sort the query by value. Each ring
sweeps clockwise from twelve o'clock, three quarters of a turn at the largest
value. Ring colors come from color_for, so a channel pinned under
style.charts.category_colors keeps its board color.
charts:
channel_rings:
type: custom
template: radial_bar
title: Sessions by channel
query:
columns: [channel, sessions]
values:
- [Search, 18400]
- [Direct, 12900]
- [Social, 8300]
- [Email, 6100]
- [Partners, 2900]
style:
aspect_ratio: 1
x: channel
y: sessions
rows:
- channel_rings
templates/radial_bar.svg.j2
{#---
description: Radial bar chart, one ring per row in query order (outermost first). Each ring sweeps clockwise from twelve o'clock, three quarters of a turn at the largest value.
channels:
x:
description: Column naming each ring.
y:
description: Column holding each ring's value.
options:
value_format:
type: string
default: ',.0f'
sweep:
type: number
default: 270
description: Degrees of arc the largest value fills.
---#}
{% set p = options %}
{% set category_col = channels.x %}
{% set value_col = channels.y %}
{% set peak = rows | map(attribute=value_col) | map("float") | max %}
{% set n = rows | length %}
{% set cx = width / 2 %}
{% set cy = height / 2 %}
{% set outer = [width, height] | min / 2 - 2 %}
{% set hole = outer * 0.28 %}
{% set step = (outer - hole) / n %}
{% set thick = step * 0.78 %}
{# Angles in radians, measured clockwise from twelve o'clock. #}
{% macro ring(r0, r1, a1, fill, tip="") %}
{% set large = 1 if a1 > math.pi else 0 %}
<path d="M{{ cx|round(2) }},{{ (cy - r1)|round(2) }}A{{ r1|round(2) }},{{ r1|round(2) }} 0 {{ large }} 1 {{ (cx + r1 * math.sin(a1))|round(2) }},{{ (cy - r1 * math.cos(a1))|round(2) }}L{{ (cx + r0 * math.sin(a1))|round(2) }},{{ (cy - r0 * math.cos(a1))|round(2) }}A{{ r0|round(2) }},{{ r0|round(2) }} 0 {{ large }} 0 {{ cx|round(2) }},{{ (cy - r0)|round(2) }}Z" fill="{{ fill }}">{% if tip %}<title>{{ tip }}</title>{% endif %}</path>
{% endmacro %}
<g font-family="{{ style.font.family }}" font-size="{{ style.charts.axis.labels.font.size }}">
{% for r in rows %}
{% set r1 = outer - loop.index0 * step %}
{% set r0 = r1 - thick %}
{% set v = r[value_col] | float %}
{% set full = p.sweep / 180 * math.pi %}
{{ ring(r0, r1, full - 0.0001, style.charts.axis.grid.color) }}
{{ ring(r0, r1, [full * v / peak, 0.001] | max, color_for(r[category_col], category_col), r[category_col] ~ ": " ~ (v | format(p.value_format))) }}
{% set ly = ((r0 + r1) / -2 + cy)|round(1) %}
{% set shown = v | format(p.value_format) %}
<text x="{{ (cx - 6)|round(1) }}" y="{{ ly }}" dy="0.35em" text-anchor="end" fill="{{ style.muted }}">{{ shown }}</text>
<text x="{{ (cx - 10 - text_width(shown, style.charts.axis.labels.font.size))|round(1) }}" y="{{ ly }}" dy="0.35em" text-anchor="end" fill="{{ style.font.color }}">{{ r[category_col] }}</text>
{% endfor %}
</g>
Radial bars exaggerate the outer rings: the same value draws a longer arc on an outer ring than on an inner one. Prefer a bar chart when the comparison must be exact.
XmR chart¶
Best for: telling routine variation from a real change in a process metric over time.
An XmR (individuals and moving range) chart plots each period's value with its mean and natural process limits, at 2.66 times the average moving range on either side of the mean. Below it, the moving range chart plots the change from one period to the next. The template flags points outside the limits in the theme's negative tone, and runs of eight or more points on one side of the mean in its warning tone.
charts:
defect_xmr:
type: custom
template: xmr
title: Defects per thousand units
query:
columns: [month, defects_per_k]
values:
- [Jan, 41.2]
- [Feb, 43.0]
- [Mar, 40.1]
- [Apr, 44.9]
- [May, 42.3]
- [Jun, 39.8]
- [Jul, 43.6]
- [Aug, 41.7]
- [Sep, 30.4]
- [Oct, 42.8]
- [Nov, 44.1]
- [Dec, 40.6]
- [Jan, 47.9]
- [Feb, 46.2]
- [Mar, 48.8]
- [Apr, 46.5]
- [May, 47.1]
- [Jun, 49.4]
- [Jul, 46.8]
- [Aug, 48.2]
- [Sep, 42.0]
- [Oct, 43.3]
x: month
y: defects_per_k
rows:
- defect_xmr
templates/xmr.svg.j2
{#---
description: XmR process behavior chart (X chart over a moving-range chart), one row per period in time order. Points outside the natural process limits, and runs of eight or more on one side of the mean, are flagged.
channels:
x:
description: Column labeling each period, in time order.
y:
description: Column holding each period's measurement.
options:
value_format:
type: string
default: ',.1f'
run_length:
type: integer
default: 8
description: Points in a row on one side of the mean that count as a signal.
---#}
{% set p = options %}
{% set x_col = channels.x %}
{% set value_col = channels.y %}
{% if rows | length < 3 %}{{ raise_error("An XmR chart needs at least three periods.") }}{% endif %}
{% set vals = rows | map(attribute=value_col) | map("float") | list %}
{% set n = vals | length %}
{% set mean = (vals | sum) / n %}
{% set ns = namespace(mr=[], side=[], run=[], rid=0) %}
{% for i in range(1, n) %}{% set ns.mr = ns.mr + [(vals[i] - vals[i - 1]) | abs] %}{% endfor %}
{% set mrbar = (ns.mr | sum) / (ns.mr | length) %}
{% set unpl = mean + 2.66 * mrbar %}
{% set lnpl = mean - 2.66 * mrbar %}
{% set url = 3.268 * mrbar %}
{% for v in vals %}
{% set s = 1 if v > mean else (-1 if v < mean else 0) %}
{% if not loop.first and s != ns.side[-1] %}{% set ns.rid = ns.rid + 1 %}{% endif %}
{% set ns.side = ns.side + [s] %}{% set ns.run = ns.run + [ns.rid] %}
{% endfor %}
{% set axis = style.charts.axis %}
{% set lab = axis.labels.font %}
{% set gutter = 64 %}
{% set pw = width - gutter - 4 %}
{% set xh = height * 0.62 - 10 %}
{% set my0 = height * 0.62 + 14 %}
{% set mh = height - my0 - 20 %}
{% set lo = [lnpl, vals | min] | min %}{% set hi = [unpl, vals | max] | max %}
{% set pad = (hi - lo) * 0.06 %}{% set lo = lo - pad %}{% set hi = hi + pad %}
{% set mhi = [url, ns.mr | max] | max * 1.08 %}
{% macro px(i) %}{{ (4 + pw * i / (n - 1))|round(1) }}{% endmacro %}
{% macro py(v) %}{{ (xh - xh * (v - lo) / (hi - lo))|round(1) }}{% endmacro %}
{% macro my(v) %}{{ (my0 + mh - mh * v / mhi)|round(1) }}{% endmacro %}
{% macro limit(y, label, value, color, dash) %}
<line x1="4" x2="{{ 4 + pw }}" y1="{{ y }}" y2="{{ y }}" stroke="{{ color }}" stroke-width="1.25"{% if dash %} stroke-dasharray="5 4"{% endif %}/>
<text x="{{ 4 + pw + 6 }}" y="{{ y }}" dy="0.35em" font-size="{{ lab.size }}" fill="{{ color }}">{{ label }} {{ value | format(p.value_format) }}</text>
{% endmacro %}
{% set ink = style.charts.color.categorical.single_series_palette[0] %}
<g font-family="{{ lab.family }}">
{{ limit(py(unpl), "UNPL", unpl, style.muted, true) }}
{{ limit(py(mean), "Mean", mean, style.muted, false) }}
{{ limit(py(lnpl), "LNPL", lnpl, style.muted, true) }}
<polyline fill="none" stroke="{{ ink }}" stroke-width="1.5" points="{% for v in vals %}{{ px(loop.index0) }},{{ py(v) }} {% endfor %}"/>
{% for v in vals %}
{% set i = loop.index0 %}
{% set out = v > unpl or v < lnpl %}
{% set in_run = ns.side[i] != 0 and (ns.run | select("equalto", ns.run[i]) | list | length) >= p.run_length %}
{% set c = style.tones.negative if out else (style.tones.warning if in_run else ink) %}
<circle cx="{{ px(i) }}" cy="{{ py(v) }}" r="{{ 4 if out or in_run else 2.5 }}" fill="{{ c }}" stroke="{{ style.background }}" stroke-width="1"><title>{{ rows[i][x_col] }}: {{ v | format(p.value_format) }}{{ " (outside limits)" if out else (" (run of " ~ p.run_length ~ "+)" if in_run else "") }}</title></circle>
{% endfor %}
<text x="4" y="{{ my0 - 4 }}" font-size="{{ lab.size }}" fill="{{ style.muted }}">Moving range</text>
{{ limit(my(url), "URL", url, style.muted, true) }}
{{ limit(my(mrbar), "Avg", mrbar, style.muted, false) }}
<polyline fill="none" stroke="{{ ink }}" stroke-width="1.25" points="{% for m in ns.mr %}{{ px(loop.index) }},{{ my(m) }} {% endfor %}"/>
{% for m in ns.mr %}
<circle cx="{{ px(loop.index) }}" cy="{{ my(m) }}" r="{{ 3.5 if m > url else 2 }}" fill="{{ style.tones.negative if m > url else ink }}"><title>{{ rows[loop.index][x_col] }}: moving range {{ m | format(p.value_format) }}</title></circle>
{% endfor %}
{% for i in [0, n // 2, n - 1] %}
<text x="{{ px(i) }}" y="{{ height - 4 }}" text-anchor="{{ 'start' if i == 0 else ('end' if i == n - 1 else 'middle') }}" font-size="{{ lab.size }}" fill="{{ lab.color }}">{{ rows[i][x_col] }}</text>
{% endfor %}
</g>
Isotype pictogram¶
Best for: counts a general audience should grasp at a glance.
Isotype was developed in 1920s Vienna by Otto Neurath, Marie Neurath, and Gerd Arntz. One symbol always stands for the same quantity, so readers compare amounts by counting, and color splits each block into groups. This example follows chart VI of a 1938 Isotype book, one figure for every 1,250 teachers in England and Wales.
Each row is one block and group. per_row_column gives each block its own
width, and the pinned category_colors keep the original's blue, red, green,
and black.
style:
charts:
category_colors:
qualification:
values:
Graduates: "#2b59a8"
Certificated: "#d33a32"
Specialists: "#2e9a6a"
Uncertificated: "#2a2a2a"
charts:
teachers_isotype:
type: custom
template: isotype
title: Teachers in public elementary and secondary schools, 1938
query:
columns: [school, qualification, teachers, per_row]
values:
- [Elementary men, Graduates, 6250, 3]
- [Elementary men, Certificated, 38750, 3]
- [Elementary men, Specialists, 2500, 3]
- [Elementary men, Uncertificated, 1250, 3]
- [Elementary women, Graduates, 3750, 7]
- [Elementary women, Certificated, 103750, 7]
- [Elementary women, Specialists, 2500, 7]
- [Elementary women, Uncertificated, 21250, 7]
- [Secondary men, Graduates, 12500, 1]
- [Secondary men, Specialists, 1250, 1]
- [Secondary women, Graduates, 8750, 1]
- [Secondary women, Certificated, 1250, 1]
- [Secondary women, Specialists, 2500, 1]
- [Secondary women, Uncertificated, 1250, 1]
style:
aspect_ratio: 1
x: school
color: qualification
y: teachers
options:
per_symbol: 1250
unit: teachers
per_row_column: per_row
rows:
- teachers_isotype
templates/isotype.svg.j2
{#---
description: Isotype pictogram after Otto and Marie Neurath, one row per block and group. Each block is a column of repeated symbols, one symbol per fixed quantity, colored by group and filled from the bottom in reverse query order.
channels:
x:
description: Column naming each block of symbols, drawn left to right in query order.
color:
description: Column naming the group a count belongs to; sets the symbol color.
y:
description: Column holding the quantity for each block and group.
options:
per_symbol:
type: number
description: Quantity one symbol stands for. Counts round to whole symbols.
unit:
type: string
description: What is counted, for the key ("teachers").
per_row:
type: integer
default: 5
description: Symbols per row in every block.
per_row_column:
type: column
default: null
description: Optional column giving each block its own symbols per row.
symbol:
type: string
default: person
description: person, square, or circle.
---#}
{% set p = options %}
{% set block_col = channels.x %}
{% set group_col = channels.color %}
{% set count_col = channels.y %}
{% if p.symbol not in ["person", "square", "circle"] %}{{ raise_error("symbol must be person, square, or circle; got " ~ p.symbol) }}{% endif %}
{% set ns = namespace(blocks=[], groups=[], seq={}, wide={}) %}
{% for r in rows %}
{% set b = r[block_col] | string %}{% set g = r[group_col] | string %}
{% if b not in ns.blocks %}{% set ns.blocks = ns.blocks + [b] %}{% endif %}
{% if g not in ns.groups %}{% set ns.groups = ns.groups + [g] %}{% endif %}
{% set k = ((r[count_col] | float) / p.per_symbol) | round | int %}
{% set ns.seq = dict(ns.seq, **{b: ns.seq.get(b, []) + [g] * k}) %}
{% set ns.wide = dict(ns.wide, **{b: (r[p.per_row_column] | int) if p.per_row_column else p.per_row}) %}
{% endfor %}
{% set key_h = 2.6 * style.font.size + 12 %}
{% set label_h = style.font.size * 2.6 + 14 %}
{% set gap_cols = 1.5 %}
{% set ns.cols = 0 %}{% set ns.rows = 0 %}
{% for b in ns.blocks %}
{% set ns.cols = ns.cols + ns.wide[b] %}
{% set ns.rows = [ns.rows, ((ns.seq[b] | length) / ns.wide[b]) | round(0, "ceil")] | max %}
{% endfor %}
{% set aspect = 2.4 if p.symbol == "person" else 1 %}
{% set cell = [width / (ns.cols + gap_cols * (ns.blocks | length - 1)), (height - key_h - label_h) / (ns.rows * aspect)] | min %}
{% set sw = cell %}{% set sh = cell * aspect %}
{% set base = height - key_h - label_h %}
{% set gap = sw * 1.5 + 8 %}
{% set ns.slot = {} %}{% set ns.used = gap * (ns.blocks | length - 1) %}
{% for b in ns.blocks %}
{% set wmax = namespace(v=0) %}{% for w in b.split(" ") %}{% set wmax.v = [wmax.v, text_width(w, style.font.size)] | max %}{% endfor %}
{% set ns.slot = dict(ns.slot, **{b: [ns.wide[b] * sw, wmax.v] | max}) %}
{% set ns.used = ns.used + ns.slot[b] %}
{% endfor %}
{% set used = ns.used %}
<defs>
<symbol id="{{ uid }}-person" viewBox="0 0 10 24">
<circle cx="5" cy="2.8" r="2.5"/>
<path d="M1.6 6.6h6.8l1.1 8.2H8.1l-.5 8.6H5.6L5 16.4l-.6 7H2.4l-.5-8.6H.5z"/>
</symbol>
</defs>
<g font-family="{{ style.font.family }}" font-size="{{ style.font.size }}" fill="{{ style.font.color }}">
{% set ns.x = (width - used) / 2 %}
{% for b in ns.blocks %}
{% set wide = ns.wide[b] %}
{% set seq = ns.seq[b] %}
{% set total = seq | length %}
{% set rem = total % wide or wide %}
{% set nrows = (total / wide) | round(0, "ceil") %}
<g>
<title>{{ b }}: {% for g in ns.groups if g in seq %}{{ g }} {{ ((seq | select("equalto", g) | list | length) * p.per_symbol) | format(",.0f") }}{{ "; " if not loop.last }}{% endfor %}</title>
{% for g in seq %}
{% set k = loop.index0 %}
{% set row = 0 if k < rem else 1 + (k - rem) // wide %}
{% set col = k if k < rem else (k - rem) % wide %}
{% set x = ns.x + (ns.slot[b] - wide * sw) / 2 + col * sw %}{% set y = base - (nrows - row) * sh %}
{% if p.symbol == "person" %}
<use href="#{{ uid }}-person" x="{{ (x + sw * 0.08)|round(2) }}" y="{{ (y + sh * 0.06)|round(2) }}" width="{{ (sw * 0.84)|round(2) }}" height="{{ (sh * 0.88)|round(2) }}" fill="{{ color_for(g, group_col) }}"/>
{% elif p.symbol == "square" %}
<rect x="{{ (x + sw * 0.1)|round(2) }}" y="{{ (y + sh * 0.1)|round(2) }}" width="{{ (sw * 0.8)|round(2) }}" height="{{ (sh * 0.8)|round(2) }}" fill="{{ color_for(g, group_col) }}"/>
{% else %}
<circle cx="{{ (x + sw / 2)|round(2) }}" cy="{{ (y + sh / 2)|round(2) }}" r="{{ (sw * 0.4)|round(2) }}" fill="{{ color_for(g, group_col) }}"/>
{% endif %}
{% endfor %}
</g>
{% set words = [b] if text_width(b, style.font.size) <= ns.slot[b] + gap - 8 else b.split(" ") %}
<text x="{{ (ns.x + ns.slot[b] / 2)|round(1) }}" y="{{ (base + style.font.size + 6)|round(1) }}" text-anchor="middle">{% for w in words %}<tspan x="{{ (ns.x + ns.slot[b] / 2)|round(1) }}" dy="{{ 0 if loop.first else '1.15em' }}">{{ w }}</tspan>{% endfor %}</text>
{% set ns.x = ns.x + ns.slot[b] + gap %}
{% endfor %}
<text x="{{ ((width - used) / 2)|round(1) }}" y="{{ (height - key_h + style.font.size + 10)|round(1) }}" fill="{{ style.muted }}">Each symbol represents {{ p.per_symbol | format(",") }} {{ p.unit }}</text>
{% set ns.kx = (width - used) / 2 %}
{% for g in ns.groups %}
<rect x="{{ ns.kx }}" y="{{ (height - style.font.size - 2)|round(1) }}" width="10" height="10" rx="2" fill="{{ color_for(g, group_col) }}"/>
<text x="{{ ns.kx + 14 }}" y="{{ (height - 3)|round(1) }}">{{ g }}</text>
{% set ns.kx = ns.kx + 14 + text_width(g, style.font.size) + 18 %}
{% endfor %}
</g>
symbol: square and symbol: circle draw plain unit charts. The person
figure is defined once as a <symbol> and placed with <use>, with its id
prefixed by uid.
Waffle¶
Best for: parts of a whole, read as whole percentages.
A 10 by 10 grid where each square is one percent. Shares round to whole squares by largest remainder, so the grid always holds exactly 100.
charts:
warehouse_waffle:
type: custom
template: waffle
title: Projects by warehouse
query:
columns: [warehouse, projects]
values:
- [Snowflake, 412]
- [BigQuery, 298]
- [Databricks, 187]
- [Redshift, 96]
- [Postgres, 61]
- [DuckDB, 38]
color: warehouse
theta: projects
rows:
- warehouse_waffle
templates/waffle.svg.j2
{#---
description: Waffle chart, a 10 by 10 grid where each square is one percent of the total, one row per category in query order. Shares round to whole squares by largest remainder, so the grid always holds exactly 100.
channels:
color:
description: Column naming each category.
theta:
description: Column holding each category's amount.
options:
value_format:
type: string
default: ',.0f'
---#}
{% set p = options %}
{% set category_col = channels.color %}
{% set value_col = channels.theta %}
{% set total = rows | map(attribute=value_col) | map("float") | sum %}
{% if total <= 0 %}{{ raise_error("A waffle needs a positive total.") }}{% endif %}
{% set ns = namespace(parts=[], cells=[], left=100) %}
{% for r in rows %}
{% set share = (r[value_col] | float) / total * 100 %}
{% set ns.parts = ns.parts + [{"name": r[category_col] | string, "value": r[value_col] | float, "n": share | int, "frac": share - (share | int), "order": loop.index0}] %}
{% set ns.left = ns.left - (share | int) %}
{% endfor %}
{# Hand the squares lost to rounding to the largest remainders. #}
{% set bumped = (ns.parts | sort(attribute="frac", reverse=true) | map(attribute="name") | list)[:ns.left] %}
{% for part in ns.parts %}
{% set ns.cells = ns.cells + [part.name] * (part.n + (1 if part.name in bumped else 0)) %}
{% endfor %}
{% set legend_w = 170 %}
{% set side = [width - legend_w - 16, height] | min %}
{% set cell = side / 10 %}
<g font-family="{{ style.font.family }}" font-size="{{ style.font.size }}">
{% for name in ns.cells %}
{% set i = loop.index0 %}
<rect x="{{ (i % 10 * cell + 1)|round(2) }}" y="{{ (i // 10 * cell + 1)|round(2) }}" width="{{ (cell - 2)|round(2) }}" height="{{ (cell - 2)|round(2) }}" rx="{{ (cell * 0.12)|round(2) }}" fill="{{ color_for(name, category_col) }}"><title>{{ name }}</title></rect>
{% endfor %}
{% for part in ns.parts %}
{% set y = loop.index0 * (style.font.size * 2.8) + 4 %}
<rect x="{{ (side + 16)|round(1) }}" y="{{ y|round(1) }}" width="12" height="12" rx="2" fill="{{ color_for(part.name, category_col) }}"/>
<text x="{{ (side + 34)|round(1) }}" y="{{ (y + 10)|round(1) }}" fill="{{ style.font.color }}">{{ part.name }}</text>
<text x="{{ (side + 34)|round(1) }}" y="{{ (y + 12 + style.font.size)|round(1) }}" fill="{{ style.muted }}">{{ (part.value / total) | format(".1%") }} · {{ part.value | format(p.value_format) }}</text>
{% endfor %}
</g>
Radar¶
Best for: comparing a few items' profiles across several measures that share one scale, such as review scores out of ten.
The query is long: one row per spoke and series. Spokes go clockwise from
twelve o'clock in query order, and each series draws a closed outline in its
color_for color. Every spoke runs from zero to max (the largest value when
unset), so rescale measures to a common unit in the query first. A series
missing a spoke, or with two rows for one, stops the render with an error.
charts:
plan_radar:
type: custom
template: radar
title: Plan review scores
query:
columns: [category, plan, score]
values:
- [Speed, Starter, 6]
- [Reliability, Starter, 7]
- [Support, Starter, 4]
- [Integrations, Starter, 5]
- [Ease of use, Starter, 9]
- [Price, Starter, 9]
- [Speed, Enterprise, 9]
- [Reliability, Enterprise, 9]
- [Support, Enterprise, 8]
- [Integrations, Enterprise, 9]
- [Ease of use, Enterprise, 6]
- [Price, Enterprise, 4]
style:
aspect_ratio: 1.2
x: category
y: score
color: plan
options:
max: 10
rings: 5
rows:
- plan_radar
templates/radar.svg.j2
{#---
description: Radar (spider) chart, one spoke per distinct x value in query order and one closed outline per series. Every spoke shares one scale from zero, so put the measures on a common scale, such as a score out of ten, in the query. Each series needs exactly one row per spoke.
channels:
x:
description: Column naming each spoke.
y:
description: Column holding each spoke's value, zero or more.
color:
description: Optional column naming each series; each distinct value draws its own outline.
default: null
options:
max:
type: number
default: null
description: Value at the outer ring. Defaults to the largest value.
rings:
type: integer
default: 4
description: Gridline rings between the center and the outer ring.
value_format:
type: string
default: ',.0f'
---#}
{% set p = options %}
{% set axis_col = channels.x %}
{% set value_col = channels.y %}
{% set series_col = channels.color %}
{% set ns = namespace(axes=[], series=[], keys=[], peak=0) %}
{% for r in rows %}
{% if r[axis_col] is none %}{{ raise_error("A row has no spoke name in " ~ axis_col ~ ".") }}{% endif %}
{% if series_col and r[series_col] is none %}{{ raise_error("A row has no series name in " ~ series_col ~ ".") }}{% endif %}
{% set a = r[axis_col] | string %}
{% set s = (r[series_col] | string) if series_col else "" %}
{% if r[value_col] is not number or r[value_col] is boolean %}{{ raise_error("Spoke " ~ a ~ (" in series " ~ s if series_col else "") ~ " has no value in " ~ value_col ~ ".") }}{% endif %}
{% set v = r[value_col] | float %}
{% if v < 0 %}{{ raise_error("A radar spoke cannot be negative: " ~ a ~ " is " ~ v ~ ".") }}{% endif %}
{% if (a, s) in ns.keys %}{{ raise_error("More than one row for spoke " ~ a ~ (" in series " ~ s if series_col else "") ~ "; aggregate in the query.") }}{% endif %}
{% set ns.keys = ns.keys + [(a, s)] %}
{% if a not in ns.axes %}{% set ns.axes = ns.axes + [a] %}{% endif %}
{% if s not in ns.series %}{% set ns.series = ns.series + [s] %}{% endif %}
{% set ns.peak = [ns.peak, v] | max %}
{% endfor %}
{% set n = ns.axes | length %}
{% if n < 3 %}{{ raise_error("A radar chart needs at least three spokes; this query has " ~ n ~ ".") }}{% endif %}
{% if ns.keys | length != n * (ns.series | length) %}{{ raise_error("Every series needs a row for every spoke.") }}{% endif %}
{% if p.max is not none and p.max <= 0 %}{{ raise_error("Option max must be above zero.") }}{% endif %}
{% set top = p.max if p.max is not none else ns.peak %}
{% if top <= 0 %}{{ raise_error("A radar chart needs a value above zero.") }}{% endif %}
{% if ns.peak > top %}{{ raise_error("A value of " ~ ns.peak ~ " is above max " ~ top ~ ".") }}{% endif %}
{% set size = style.font.size %}
{% set small = style.charts.axis.labels.font.size %}
{% set legend_h = size * 2.4 if series_col else 0 %}
{% set lw = namespace(w=0) %}
{% for a in ns.axes %}{% set lw.w = [lw.w, text_width(a, small)] | max %}{% endfor %}
{% set ph = height - legend_h %}
{% set cx = width / 2 %}
{% set cy = ph / 2 %}
{% set rad = [width / 2 - lw.w - 10, ph / 2 - small * 1.8] | min %}
{% if rad <= 0 %}{{ raise_error("The chart is too small to fit its spoke labels.") }}{% endif %}
{% set step = 2 * math.pi / n %}
{% macro pt(i, r) %}{{ (cx + r * math.sin(step * i))|round(2) }},{{ (cy - r * math.cos(step * i))|round(2) }}{% endmacro %}
<g font-family="{{ style.font.family }}">
{% for k in range(1, p.rings + 1) %}
{% set rr = rad * k / p.rings %}
<polygon points="{% for i in range(n) %}{{ pt(i, rr) }} {% endfor %}" fill="none" stroke="{{ style.charts.axis.grid.color }}"/>
<text x="{{ (cx + 4)|round(1) }}" y="{{ (cy - rr)|round(1) }}" dy="-0.3em" font-size="{{ small }}" fill="{{ style.muted }}">{{ (top * k / p.rings) | format(p.value_format) }}</text>
{% endfor %}
{% for a in ns.axes %}
{% set i = loop.index0 %}
{% set sx = math.sin(step * i) %}
{% set sy = math.cos(step * i) %}
<line x1="{{ cx|round(2) }}" y1="{{ cy|round(2) }}" x2="{{ (cx + rad * sx)|round(2) }}" y2="{{ (cy - rad * sy)|round(2) }}" stroke="{{ style.charts.axis.grid.color }}"/>
<text x="{{ (cx + (rad + 8) * sx)|round(1) }}" y="{{ (cy - (rad + 8) * sy + small * 0.5 * (1 - sy) - (small * 1.3 if sy > 0.9 else 0))|round(1) }}" dy="{{ '-0.15em' if sy > 0.5 else '0.2em' }}" text-anchor="{{ 'middle' if sx | abs < 0.1 else ('start' if sx > 0 else 'end') }}" font-size="{{ small }}" fill="{{ style.font.color }}">{{ a }}</text>
{% endfor %}
{% for s in ns.series %}
{% set c = color_for(s, series_col) if series_col else style.charts.color.categorical.single_series_palette[0] %}
{% set vs = namespace(list=[]) %}
{% for a in ns.axes %}
{% for r in rows if (r[axis_col] | string) == a and (not series_col or (r[series_col] | string) == s) %}
{% set vs.list = vs.list + [r[value_col] | float] %}
{% endfor %}
{% endfor %}
<g>
<polygon points="{% for v in vs.list %}{{ pt(loop.index0, rad * v / top) }} {% endfor %}" fill="{{ c }}" fill-opacity="0.15" stroke="{{ c }}" stroke-width="2" stroke-linejoin="round"/>
{% for v in vs.list %}
{% set rr = rad * v / top %}
<circle cx="{{ (cx + rr * math.sin(step * loop.index0))|round(2) }}" cy="{{ (cy - rr * math.cos(step * loop.index0))|round(2) }}" r="3.5" fill="{{ c }}" stroke="{{ style.background }}"><title>{{ (s ~ ", ") if series_col else "" }}{{ ns.axes[loop.index0] }}: {{ v | format(p.value_format) }}</title></circle>
{% endfor %}
</g>
{% endfor %}
{% if series_col %}
{% set lx = namespace(x=0) %}
{% for s in ns.series %}
<rect x="{{ lx.x|round(1) }}" y="{{ (height - size - 2)|round(1) }}" width="10" height="10" rx="2" fill="{{ color_for(s, series_col) }}"/>
<text x="{{ (lx.x + 14)|round(1) }}" y="{{ (height - 3)|round(1) }}" font-size="{{ size }}" fill="{{ style.font.color }}">{{ s }}</text>
{% set lx.x = lx.x + 14 + text_width(s, size) + 22 %}
{% endfor %}
{% endif %}
</g>
A radar's area grows with the square of its values and depends on the order of the spokes, so read it for shape, not size. Prefer a grouped bar chart when the comparison must be exact, and keep to two or three series.