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
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
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
SumIffunction is where we enter the column we want to sum, wrapped in square braces:
- The second part of the
SumIffunction is where we write the condition to specify which rows should be summed up. What we’re doing here is using the
betweenfunction to specify that we only want to add up the
Subtotalfor 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 the
[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
betweenfunction, 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
Or just copy from here:
SumIf([Subtotal], between([Created At], "2018-01-01", "2018-12-31"))
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:
Did this article help you?
Thanks for your feedback!