How to create a SQL filter variable to change date grouping between month/week/day

Get Help
1

I have a dashboard with a mix of SQL and metabase query questions. By default all the time based graphs are grouped by month. I want to create a filter for the dashboard so the filter can change the grouping freely between day, week and month.

I can see it's set up for standard metabase queries

metabase%20pic
metabase%20pic1681×545 14.8 KB
but how do I get this for SQL questions?

2

Hi @Alexhs
Use the SQL question as Saved Question:
https://www.metabase.com/docs/latest/users-guide/custom-questions.html#picking-your-starting-data
It would not be possible to add a grouping directly on SQL unless Metabase could parse the SQL.

1 Like
3

Hi @flamber
Thanks for the response, sorry what do you mean? My SQL is already a saved question that sits in the dashboard.

I've found an awkward solution to my issue by putting the variable in the date_trunc section.
date_trunc({{period}}, CAST("public"."submitted_at" AS timestamp)) AS "Date".
(submitted_at is just a timestamp for the events I'm measuring in metabase.)

This then allows me to enter "month", "week" or "day" into the variable box to group it as such but I have to type the whole thing out. Is there a way to turn it into just a dropdown menu?

4

@Alexhs If you just return all results in SQL, then you can use it as a base GUI question - that's what I mean.
Yes, it's a workaround for some databases to do the grouping in SQL.
You would have to have a lookup table to have a dropdown.

1 Like
5

@Flamber ah I see what you mean, and yeah if having a lookup table is the only solution I'll probably set that up. There's no other way is there to set a dropdown filter box with custom values that doesn't require a lookup table?

6

@Alexhs Currently no, but there's requests for that - upvote by clicking :+1: on the first post of an issue:
https://github.com/metabase/metabase/issues/5245
https://github.com/metabase/metabase/issues/6820

1 Like
7

great thanks @flamber!

8

Here is a hack for mySQL (modify accordingly for other DB's like clickhouse, postgreSQL):

Step 1: Define a view with datecode, dateformats (feel free to customize),

DROP VIEW dateCodes_v;
CREATE VIEW dateCodes_v AS
    SELECT 'daily_dow_short' AS dateCode, '%m-%d (%a)' AS dateFormat
    UNION 
    SELECT 'daily_dow' AS dateCode, '%Y-%m-%d (%a)' AS dateFormat
    UNION
    SELECT 'daily' AS dateCode, '%Y-%m-%d' AS dateFormat
    UNION
    SELECT 'weekly' AS dateCode, '%Y-%m w%u' AS dateFormat
    UNION
    SELECT 'monthly' AS dateCode, '%Y-%M' AS dateFormat
    UNION
    SELECT 'yearly' AS dateCode, '%Y' AS dateFormat
    UNION
    SELECT 'toDate' AS dateCode, 'toDate' AS dateFormat

Step 2: Use Mysql @variable to define the dateformat, and make it into a filter in metabase, e.g.,

SELECT
    @dateFormat:= (select dateFormat FROM dateCodes_v WHERE {{ dateCode }}) AS dateFormat,
    DATE_FORMAT(somedate,  @dateFormat) AS dateStr,
    count(*) AS total
GROUP BY
        DATE_FORMAT(somedate,  @dateFormat)
ORDER BY
        DATE_FORMAT(somedate,  @dateFormat)

Viola, you have various choices of filtering from the metabase GUI!

1 Like
9

Thanks @zhangguiyu!

that looks like a good way of doing it, I'll give it a test at some point and see if it works out

10

Hello @Alexhs!!
I am right now in quite a similar situation as you were here. Could I ask you how did you resolve this problem finally?
Thanks!