In version 0.35 we added a bunch of new functions to the custom column, custom filter, and custom aggregation expression formula editors. These let you do string manipulation on columns, construct filters that use ORs, calculate the share of a portion of your data relative to the whole, and much more. If you want to dig into all of the new functions that are available, check out the documentation.
Another powerful but perhaps not obvious thing these new functions let you do is create time series comparisons. For example, if you wanted to compare your revenue per month or users per day in 2019 vs. 2018, overlaid on top of each other, the new
CountIf aggregation functions let you. Here’s how.
Step 1: start a new custom question
All of these new functions are found in the custom question or “notebook” editor. To get there, we’ll click on the big “New question” button in the main navigation, then “Custom question.” Then we’ll pick the table that has the data that we have a question about. We’ll be using the sample “Orders” table that’s part of the sample dataset that comes with Metabase.
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:
We hope that this opens up all sorts of new possibilities for you and your team, especially for folks who are maybe comfortable with Excel but not so comfortable with SQL. If you’d like to show us what you’re doing with version 0.35, go ahead and say hi to us on Twitter — we’d love to see what you’re up to.
Cheers, The Metabase Team