This month VS Last month graph?

[Marcio]

How can I compare my this month's transactions with the last month's transactions? I've created 2 questions to get the number of transactions this month and last month, when putting then together in my dashboard, I was expecting to see the bars for July 1st and August 1st (and all the other days) beside to each other, for comparison. This is the result I got, the whole July's transactions in green and this month's transactions in blue.

This is essential in many reporting when comparing week on week, year on year figures, for example.

Any idea on how to create this kind of graph?

Thanks!

[poggen]

Did you manage to solve this, @Marcio? I have the same issue, but with sales during the last 30 days vs. same period last year.

[sameer]

We don’t allow you to manipulate the x-axis in that way.

What you can do is do 2 breakouts, 1 by month and one by day of the month, and you should be able to chart that as a bar chart.

[bcb]

This is an essential feature! Almost everything worth graphing in our business involves a comparison of current period vs prior period. See Google Analytics for a good example of how it’s implemented.

Are there any plans to allow users to “manipulate the x-axis in that way” going forward?

This feature will be used by almost everyone, and is basic in Google Analytics for that reason.

Will be great news if Metabase can find a way to make it happen.

[EvaS]

Hi,

I managed to do last year vs. this year (or last month vs this month) comparisons, maybe the SQL code will work for you too:

Note: this code is for average donation per quarter (the last 4 quarters) vs the same quarters last year.

This is how it will look like as table: (when you want to have a graph just remove the two middle columns and you can create one - take care to revome the concat function before to get the real numbers without the "%"

Durchschnittsspende = Average Donation
Wachstum zum Vorjahr = Growth compared to the year before
Spendenquartal = Donation quarter (date)

And that is the code (I will translate some of the fields to english to make clear what I mean - otherwise just ask :))

SELECT Spendenquartal, t2017.sum AS Durchschnittspende_2017, t2016.sum AS Durchschnittspende_2016, concat(round((t2017.sum-t2016.sum)/t2016.sum*100), "%") AS Wachstum_zum_Vorjahr
FROM
(
SELECT sum(donation_sum)/count(donation_id) AS sum, quarter(date) AS quarter1, concat(date_year, " - Q", quarter(date)) AS Spendenquartal
FROM donation_table
WHERE str_to_date(concat(date_format(donation_table.date, '%Y-%m'), '-01'), '%Y-%m-%d') BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -4 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(date_add(now(), INTERVAL -1 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY quarter1
Order by date_year asc, quarter(date) asc
) AS t2017
LEFT JOIN
(
SELECT sum(donation_sum)/count(donation_id) AS sum, quarter(date) AS quarter2
FROM donation_table
WHERE str_to_date(concat(date_format(donation_table.date, '%Y-%m'), '-01'), '%Y-%m-%d') BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -8 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
AND str_to_date(concat(date_format(date_add(now(), INTERVAL -5 quarter), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY quarter2
Order by date_year asc, quarter(date) asc
) AS t2016
ON t2017.quarter1 = t2016.quarter2

[EvaS]

Hope that helps!

[maz]

Yep, this is a feature we’re very interested in, and there are two ways we’re thinking about approaching this:

1. A simple toggle on a GUI question to include the previous period
2. The ability to add additional metrics/series to any time series question

[bcb]

Thanks, Eva.
Yes, I will do something similar to your suggestion, albeit with day to day comparisons with an offset (rather than quarter to quarter).
Custom SQL is the only way to go for this.
It will be great when it is built in.

[bcb]

Maz, it sounds like both approaches might be useful. I really do think this is a vital piece of functionality. Most stats are relative, after all.

In the case of Option 1, an input for the start date of the previous period (so that it is customizable) will be useful. We usually compare our daily stats with the equivalent period in the previous year, with an offset so that the days of the week align. For example, we might graph sales for Mon 4 Sept 2017 to Sun 1 Oct 2017 against sales for Mon 5 Sept 2016 to Sun 2 Oct 2016.

[Maverick]

Any update on the feature of providing comparison with previous period. I can use X-ray for a saved time-series question but can’t show it over the Dashboard. Any way to do this?

[maz]

No specific updates other than this is definitely something that’s figuring into our plans and designs for where we’re going with querying and charting. We’re still thinking this is something that could appear automatically or via a toggle on time series charts by default, and we’re also thinking about ways to allow users to take charts generated from x-rays and put them on a dashboard etc.

[kimroen]

We were looking for this as well this past month. In particular, we wanted to show:

• Numbers for this month vs the same month last year
• Percent change for the number

Just wanted to contribute a use-case!

[farhadmn]

You might loose the exact date (month and year) but you can extract day from date by using DAY() function in mysql.
or you can Extract month and day from date
like

CONCAT(EXTRACT(MONTH FROM DATE(`employer`.`created_at`)),"-",EXTRACT(DAY FROM DATE(`employer`.`created_at`))) AS `created_at`

[sbelak]

For posterity: we now have a feature called “smart number” which shows growth https://www.metabase.com/blog/0.31/

[donovanm]

While smart number shows growth, it's vital for reporting companies to have period vs period views.
Almost everything we do is also based off of these charts and we have historically used tableau and powerBI for these since it's baked in -

Month over Month, Year over Year, Week over Week on a dashboard level is what is needed.

Having to maintain this manually is a pain!

[flamber]

@donovanm Have a look at https://www.metabase.com/learn/dashboards/compare-times

And https://github.com/metabase/metabase/issues/5817 - upvote by clicking on the first post