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 custom question
In the upper right of the navigation bar, we’ll click on the big Ask a question button, then select Custom question. We’ll select the Sample Dataset included with Metabase, 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 we’ll 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, though, if you’re interested in counting the rows in your table over time instead of summing a column.
So to add our first metric, in the “Summarize” area of the notebook 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 Subtotal 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 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 plus (+) 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
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 just 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 users table during March of 2020 and compare that to users in March of 2019 by writing two expressions like this:
countif( between( [Created At], "2020-03-01", "2020-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 result, visualized as an area chart: