This has been an issue for me multiple times already.
Table 1 has all the orders of our company, including timestamps and order value.
Table 2 has our monthly targets for total order value. (Month, Year, Target Order Volume)
I want to build a chart showing the actual vs. target order volume PER MONTH.
In an SQL query, I can easily do a "group by" in Table 1 by month & year and THEN join it with Table 2, on month & year.
In the query editor, I have no idea how to do it. If I simply join the two tables on month & year, the resulting table obviously gets blown out of proportions.
If you make sure that you table2 returns a date (
YEAR-MONTH-01), then you should be able to do something like this:
It would probably be easier if you showed your SQL. Alternatively, you could still use SQL, but just create a Model from it.
First of all thanks for getting back so quickly, Flamber!
The problem is that I actually need to "group by" both tables before joining them. And this is what does not seem to be possible in the query editor, right?
This is because in "Table 2", there are monthly targets for total order value for every one of our branches and "Table 1" has all the orders of all our branches.
So I need to "group by" both tables for branch, month and year before joining them.
I don't want to do it in SQL because eventually, I want to show order volume vs. target order volume by month and be able to filter by branch.
Also, I have many of these cases, some being much more complex, and creating a new model for every single subquery seems very inefficient and will clutter my Metabase.
In SQL, I did it like this (variables freely translated from german):
SELECT str_to_date(concat(goals.Year,'-',goals.Month,'-','01'), '%Y-%m-%d') as Date, goals.branch, goals.Month, goals.Year, order_total, order_goal
SELECT order_goals.branch, month(order_goals.Month) as Month, year(order_goals.Month) as Year, sum(order_goals.goal) as order_goal
WHERE order_goals.branch != "SE001"
AND order_goals.branch != "DE003"
GROUP BY order_goals.branch, month(order_goals.Month), year(order_goals.Month)
) as goals
LEFT JOIN (
SELECT deals.branch, month(deals.ordered_at) as Month_Order, year(deals.ordered_at) as Year_Order, sum(deals.Total) as order_total
WHERE order = true
GROUP BY deals.branch, month(deals.ordered_at), year(deals.ordered_at)
) as orders
ON goals.branch = deals.branch
AND goals.Month = orders.Month_Order
AND goals.Year = orders.Year_Order
WHERE Year = 2022
AND order_total > 0
ORDER BY Year, Month ASC
@timsiegelkow Okay, create two questions, Goals and Orders, then create a third question which uses Goals as the base joining in Orders.
Hm okay. Don't you agree it would make sense to have subquery editing in the query editor??
@timsiegelkow It's all about balancing easy-of-use with complexity. There are some feature requests for being able to handle things on sub-query level, example: https://github.com/metabase/metabase/issues/11869
But I'm unsure if you are not able to do it with something like this: