I’ve got a chart now which charts data from previous month, month before prev. and the previous month last year. Its a bar chart grouped by client and by the above. So a bar chart where each client has three bars grouped together.
BTW: the data is already grouped via the raw SQL but I suppose for the charting engine I had to do the grouping again in UI.
I have “current month”, “previous month”, & “last year” which is a little confusing.
I’d rather have these be the month name and year. I do have a column in the output which is the month year labels eg: “Sept 2025” etc.. and I did try to use this. but I wonder if next month the chart would get confused because the month names changed.
It would be handy if the labels could be an expression with a calculation in them to_char(now() - 1 month,Mon YYYY) . it would be wonderful.
Any tips appreciated.
Dynamic column names require Dynamic SQL programming techniques, which is not something you can do from Metabase easily. Instead of making the periods columns, pivot the month columns into rows, where you can control the category name. The bar chart will create the breakout based on the category column. The breakout is calculated when the chart is rendered. The only thing that might change is the colors for the columns, as the charts remember previous series and assign new colors for new series.
For example, I’m using this query (a little more complicated to produce the desired output, but the key part is that to_char() call):
with cte as (
select remote_addr,
date_trunc('month', request_time) as mo,
sum(body_byte_sent) as total_bytes
from accesslog group by 1,2 order by 1,2
)
select remote_addr,
to_char(mo, 'Month') as month,
total_bytes
from cte
where remote_addr in (
select remote_addr from cte where remote_addr << '2a03:2880:3ff::/48'
group by 1 having count(*) = 3
)
The query outputs this:
When you plot it, use the category column as your breakout:
Now your charts will have the series labeled by date.
(Editor’s note: IPv6 addresses are from Facebook URL validators, they’re public addresses.)
EDIT: Oops, didn’t notice it was sorting by month name. Fixed it.
This is essentially what I have done.
My query relates to the fact that the names will change each month. I can guess/assume that metabase will be OK with that and won’t rearrange the order of the bars etc.
Unless told otherwise, Metabase uses the sort order of the incoming series, so as long as you’re sorting them in your query they should order fine. At least, that’s what happens for my charts with changing dimensions.
