Time intelligence
Year-to-date, quarter-to-date, month-to-date, and comparisons with last year.
Time intelligence answers the questions every business dashboard eventually asks: how are we doing so far, and how does that compare with last year?
What is available
| Calculation | What it gives you |
|---|---|
| Year to date | The running total, resetting each January |
| Quarter to date | The same, resetting each quarter |
| Month to date | The same, resetting each month |
| Same period last year | The value one year back on the axis |
| % change vs last year | That comparison as a percentage |
They need a date axis
Every one of these is computed along the chart's date axis. On a chart with no date axis they are
refused with a message rather than silently returning something else — because date_trunc on a
text column is an error, and hiding that behind a blank cell would be worse.
The axis has to be finer than the reset
"Year to date" on a yearly axis would just restate each point, so it is refused. Year-to-date needs at least a quarterly axis, quarter-to-date at least monthly, month-to-date at least daily.
How "last year" is matched
By date, not by counting rows back. The engine asks for the point exactly one year earlier on the axis, which is exact at every granularity.
Counting rows back is the obvious implementation and it is wrong twice over: 365 rows is not a year in a leap year, and 52 weeks is not a year at all. Matching by date means 1 March 2024 compares with 1 March 2023 rather than with whatever happens to sit 365 rows away.
The first year is blank
Deliberately. In the first year of your data there genuinely is no prior year to compare with, and a blank says that honestly where a zero would look like a real, catastrophic decline.