Lesson
Time series comparisons
How to do year-over-year or month-over-month comparisons using custom expressions.
A powerful but perhaps not obvious thing that custom expressions let us do is to create time series comparisons. For example, if we wanted to compare our revenue per month or users per day in 2019 vs. 2018, overlaid on top of each other, we could use SumIf
and CountIf
aggregation functions.
Step 1: start a new question
In the upper right of the navigation bar, we’ll click on the + New button, then select Question. We’ll select Raw data > Sample Database, and choose the Orders
table.

Step 2: add your first metric
In our example, we’re going to sum the Subtotal
column in our Orders
table to get our total revenue, and then break that number out by month. But the one different thing we’re going to do is create a separate metric for each year that we want to see, using the SumIf
function. (You could just as easily use the CountIf
function if you’re interested in counting the rows in your table over time instead of summing a column.)
To add our first metric, in the Summarize area of the Notebook Editor we’ll click the button that says Pick the metric you want to see
and scroll down to the Custom Expression option.

In the box that pops up, we’ll write the following:
SumIf([Subtotal], between([Created At], "2019-01-01", "2019-12-31"))
What this is saying in effect is, “sum up the Subtotal
column for all rows where the Created At
column is between January 1, 2019, and December 31, 2019.” Let’s look at the specific parts of this expression individually:
- The first part of the
SumIf
function is where we enter the column we want to sum, wrapped in square braces:[Subtotal]
. - The second part of the
SumIf
function is where we write the condition to specify which rows should be summed up. What we’re doing here is using thebetween
function to specify that we only want to add up theSubtotal
for orders that were placed within 2019. The reason we’re doing this here instead of in a filter is so that each additional metric we add can specify a different time range. In thebetween
function,[Created At]
is the time column in our table that we’re using to specify the time range. The first date in quotes is the start date of our range, and the second is the end date. - In the
between
function, we have to write the dates in this format:YYYY-MM-DD
. I.e., four characters for the year, then two for the month, then two for the day, all separated by dashes. Note that you always need to use two characters for the month and the day: you have to write “2019-01-01” not “2019-1-1”.
Then we’ll click Done to add our metric.
Step 3: repeat step 2 for each additional year you want to compare
To add additional years to our comparison, we’ll click the +
button and add an additional custom expression for each year. The easy way to do this is to copy the first expression we added in step 2, then paste that into each new expression and modify the year part of the between
function.
Or just copy from here:
2018:
SumIf([Subtotal], between([Created At], "2018-01-01", "2018-12-31"))
2017:
SumIf([Subtotal], between([Created At], "2017-01-01", "2017-12-31"))

Step 4: break out by month-of-year

Next we need to click Pick a column to group by
to choose our breakout column. In most situations we’d pick a time column and choose the By month
granularity option. But in this case, in order to overlay the series on top of each other, we’re going to choose the Month of year
option. (If you were to choose Month
instead of Month of year
, you’d see the series next to each other, one after the other.)
To do that, we’ll hover over the Created At
date column and click By month
to show the rest of the granularity options, then pick Month of year
.

Step 5: visualize the result!
To see our results, we click on the Visualize button at the bottom of the notebook. Metabase will automatically show our results as a bar chart, with a different series for each year, broken out by each month of the year.

If you’d rather see this as a line chart, you can click the Visualization button in the bottom left of the screen to see your options.
Taking this further
You’re not limited to doing year-over-year comparisons with this pattern. You can compare any metric over an arbitrary period of time to another, just by changing the dates within the between
function, and the granularity of the date grouping. For example, we could count the rows in our People
table during March of 2019 and compare that to users in March of 2018 by writing two expressions like this:
countif( between( [Created At], "2018-03-01", "2018-03-31") )
countif( between( [Created At], "2019-03-01", "2019-03-31") )
We’d then break these two metrics out by Created At
with the granularity set to “Day of Month” in order to overlay the two series on top of each other. Here’s the notebook:

Here’s the result, visualized as an area chart:

You can adjust the grouping easily with the time series chrome at the bottom of the chart:
