Comparison bars (SQL)

A Comparison bars panel puts two time periods side by side, per category — this week next to last week, this month next to the same month last year, or any other offset you pick. Use it whenever the question is “is this actually up or down” rather than just “what is it right now” — a plain time series shows the trend line, but a reader still has to eyeball two different points on it; comparison bars put the two numbers you actually care about right next to each other, plus the delta.

Query convention

SELECT event_class AS name, count() AS valueFROM chomper.eventsWHERE timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})GROUP BY name ORDER BY name

Same two-column shape as a Pie panel — name (the category) and value (its count) — because it's the same query, run twice: once with {from}/{to} bound to your dashboard's current time range, once with them shifted back to the comparison period you picked. You never write two queries; the panel runs the second one for you. A query with no GROUP BY at all still works — it just renders as a single pair of bars instead of several.

Choosing the comparison period

The Compare to dropdown decides how far back the second period is shifted — not its length, which always matches whatever time range the dashboard itself is set to:

• Day over day — exactly 24 hours earlier.
• Week over week — exactly 7 days earlier.
• Month over month — one real calendar month earlier, not a fixed 30 days. A range ending on March 31st compares against February 28th (or 29th), instead of overflowing into March.
• Year over year — one real calendar year earlier, with the same leap-year clamp (Feb 29 falls back to Feb 28 in a non-leap year).
• Custom — any number of minutes, hours, days, or weeks back.

This is why “month over month” and “year over year” are worth having as their own presets instead of just a custom day count — -30 days would quietly drift away from meaning “last month” the moment a 31-day month is involved, and would be flat-out wrong across a leap year.

Example: comparing by category — this week's channel mix vs last week's

SELECT event_class AS name, count() AS valueFROM chomper.eventsWHERE event_class IN ('call', 'chat', 'email', 'visit')  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})GROUP BY name ORDER BY name

With Week over week selected, this draws one pair of bars per interaction type — calls, chats, emails, office visits — each showing this week's count next to last week's. It answers “which channel actually grew or shrank” at a glance, instead of just “total interactions changed by X%”, which can hide one channel quietly dropping while another one picks up the slack.

Example: comparing by day of week — is Monday always this busy?

SELECT  multiIf(toDayOfWeek(timestamp) = 1, 'Monday',          toDayOfWeek(timestamp) = 2, 'Tuesday',          toDayOfWeek(timestamp) = 3, 'Wednesday',          toDayOfWeek(timestamp) = 4, 'Thursday',          toDayOfWeek(timestamp) = 5, 'Friday',          toDayOfWeek(timestamp) = 6, 'Saturday',          'Sunday') AS name,  count() AS valueFROM chomper.eventsWHERE event_class = 'email'  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})GROUP BY nameORDER BY toDayOfWeek(anyLast(timestamp))

Same panel type, a completely different category — here name is a day of the week instead of an event type, and the query is scoped to a single event type (support emails). There's nothing special about “category” from the panel's point of view: it's whatever your GROUP BY produces, so grouping by weekday, by country, by plan tier, or by anything else you can express in SQL works exactly the same way.

Keeping today's weekday on the right

The example above always lists Monday first. If you'd rather the chart read as a rolling week that always ends on today — so checking in on a Wednesday still puts "today" on the right instead of buried in the middle — sort by how many days ago each weekday last occurred instead of its fixed Monday–Sunday number:

SELECT  multiIf(toDayOfWeek(timestamp) = 1, 'Monday',          toDayOfWeek(timestamp) = 2, 'Tuesday',          toDayOfWeek(timestamp) = 3, 'Wednesday',          toDayOfWeek(timestamp) = 4, 'Thursday',          toDayOfWeek(timestamp) = 5, 'Friday',          toDayOfWeek(timestamp) = 6, 'Saturday',          'Sunday') AS name,  count() AS valueFROM chomper.eventsWHERE event_class = 'email'  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})GROUP BY name, toDayOfWeek(timestamp)ORDER BY (toDayOfWeek(today()) - toDayOfWeek(timestamp) + 7) % 7 DESC

(toDayOfWeek(today()) - toDayOfWeek(timestamp) + 7) % 7 is “how many days ago this weekday last occurred” — 0 for today, up to 6 for the day whose weekday happened longest ago. Sorting that descending puts the stalest day first and today last, so the rightmost bar is always today’s weekday, whichever day you happen to open the dashboard on. today() is evaluated fresh on every run, so the rotation keeps itself up to date — nothing here ever needs updating as real days pass.

Reading the chart

Each category gets two bars — the comparison period on the left, the current period on the right, oldest to freshest reading left to right. Colors for both are configurable independently.

Below the chart: a legend (each period's total, toggleable on its own), a trend line (a diagonal arrow plus the change as a count and/or a percentage, toggleable independently from the legend), and a Summary calculation setting — Total, Average, or Maximum — that decides how those per-period numbers collapse every category into one. This only affects the legend/trend numbers: each bar on the chart, and the tooltip you get from hovering one, always show that category's own real value.

If the dashboard's actual time range is a lot shorter than what the chosen preset's name usually implies — “Last 24 hours” paired with “Week over week”, say — a small warning-triangle icon appears next to the trend; hover it for the explanation. The comparison itself is still computed correctly either way, it's just a nudge that the numbers might be easy to misread.

Drilling down into a bar

Click either bar for a category to see the underlying rows, same as any other SQL panel — bind the clicked category with {bucket:String}:

SELECT * FROM chomper.eventsWHERE event_class = {bucket:String}  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})ORDER BY timestamp DESCLIMIT 100

The one thing that's different from every other panel type: {from}/{to} are bound to whichever bar you actually clicked, not always the dashboard's live range. Click the current-period bar and you get that period's rows; click the comparison-period bar and you get the comparison period's rows instead — both from the exact same drill-down query, no extra setup needed.