Dynamic date grouping by day, week, month, quarter... on dashboards (and all questions)

[benjR]

Hi there!

On some queries (I think the ones made with the query builder) you can change the grouping on dates "by day, by month, by year", it would be really useful to be able to do that at the dashboard level the same way you filter by date.

Today to have a dashboard with datas grouped by month or by day or by week, or by quarter, I don't see any option other than duplicating many times the same question.

Do you think it would be doable ?

Thanks and keep up the good work!

:arrow_down: Please click the :+1: reaction instead of leaving a +1 or update? comment

[alberthkcheng]

+1

[etiennecallies]

+1. Especially as this feature is already existing when viewing a single question.

[tannerhearne]

+1

[lequi]

+1

[mazameli]

We'd love it if everyone could please use the 👍 reaction on the issue description rather than leaving +1 comments, please. This helps us to prioritize issues, and keeps notification spam to a minimum.

[citizen-seven]

Any updates here?

[GitBronch]

Until this feature gets released you can use this workaround:

GROUP BY (CASE {{aggregation}} WHEN "d" THEN datefield WHEN "w" THEN week(datefield) WHEN "m" THEN month(datefield) ELSE datefield

END )

{{aggregation}} is a text field (required, default "d" ) and 'datefield' being the column where the date is stored in your table

Then you can create a dashboard filter (text filter) and link it to {{aggregation}} in the question.

[etiennecallies]

Thanks for the trick!

On Mon, 14 Oct 2019, 17:20 GitBronch, notifications@github.com wrote:

Until this feature gets released you can use this workaround:

GROUP BY (CASE {{aggregation}} WHEN "d" THEN datefield WHEN "w" THEN week(datefield) WHEN "m" THEN month(datefield) ELSE datefield

END )

{{aggregation}} is a text field (required, default "d" ) and 'datefield' being the column where the date is stored in your table

Then you can create a dashboard filter (text filter) and link it to {{aggregation}} in the question.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/metabase/metabase/issues/6583?email_source=notifications&email_token=AB2H4UXXS6S4TQL2RDUCCALQOSE2FA5CNFSM4EINBVEKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBFFJRQ#issuecomment-541742278, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB2H4UW4YSAEKQEJY5KM4V3QOSE2FANCNFSM4EINBVEA .

[vkambhampati493]

Another way I found to do this:

date_trunc({{duration}}, column)

The duration filter can be set to filter. Then I put a note on the dashboard saying to only enter in year, quarter, month, week or day in the text filter.

I found this works if not a little bit more work on the user's side.

[benjR]

hi @salsakran still planned in 0.38 ? thanks!

[flamber]

@benjR No, this won't make it for 38. I don't have a timeline for this yet.

[marcoruggine]

Hi guys,

I've never found the time to write a proper post/article on this but I found a solution to this problem when building custom SQL queries using BigQuery syntax.

What I want as result is that in a dashboard/question my users can filter freely by date and my grouping will adjust automatically guessing my preference.

How I do it is basically extract the information I need from the {{time}} filter and then use it in later steps. Hope you like my DIY solution:

1) setting up a rule to use later on for dynamic time-grouping

WITH grouping_rule as ( SELECT -- I use these just to debug and visually see what is in the time filter since I work in UTC and non-UTC min(created_at) as min, max(created_at) as max, TIMESTAMP_DIFF(max(created_at), min(created_at), day) as diff, -- this is the real condition to dynamic groping CASE WHEN TIMESTAMP_DIFF(max(created_at), min(created_at), day) >= 366 then 'year' WHEN TIMESTAMP_DIFF(max(created_at), min(created_at), day) >= 31 and TIMESTAMP_DIFF(max(created_at), min(created_at), day) < 366 then 'month' WHEN TIMESTAMP_DIFF(max(created_at), min(created_at), day) >= 8 and TIMESTAMP_DIFF(max(created_at), min(created_at), day) < 31 then 'week' WHEN TIMESTAMP_DIFF(max(created_at), min(created_at), day) >= 1 and TIMESTAMP_DIFF(max(created_at), min(created_at), day) < 8 then 'day' WHEN TIMESTAMP_DIFF(max(created_at), min(created_at), day) < 1 then 'minute' END as rule, FROM my_table ),

2) then I create my table, I could stop here but I added a 3rd step to customize time formats

final_table as ( SELECT CASE WHEN rule = 'year' then TIMESTAMP_TRUNC(created_at, year) WHEN rule = 'month' then TIMESTAMP_TRUNC(created_at, month) WHEN rule = 'week' then TIMESTAMP_TRUNC(created_at, week) WHEN rule = 'day' then TIMESTAMP_TRUNC(created_at, day) WHEN rule = 'minute' then TIMESTAMP_TRUNC(created_at, minute) END as time_to_format, [...] as metric_1, [...] as metric_2, [...] as metric_N FROM my_table GROUP BY time_to_format (and eventually other metrics) ORDER BY time_to_format ASC )

3) finally, format everything according to what I like

SELECT CASE WHEN (SELECT rule FROM grouping_rule) = 'year'THEN FORMAT_TIMESTAMP( "%Y", time_to_format) WHEN (SELECT rule FROM grouping_rule) = 'month'THEN FORMAT_TIMESTAMP( "%b %Y", time_to_format) WHEN (SELECT rule FROM grouping_rule) = 'week'THEN FORMAT_TIMESTAMP( "Week %d %b", time_to_format) WHEN (SELECT rule FROM grouping_rule) = 'day'THEN FORMAT_TIMESTAMP( "%a %d %b", time_to_format) WHEN (SELECT rule FROM grouping_rule) = 'minute'THEN FORMAT_TIMESTAMP( "%H:%M", time_to_format) END as time, metric_1, metric_2, metric_N FROM final_table ORDER BY time_to_format ASC

[siregarfaisal28]

+1 really need this feature

[williamkaper]

Another way I found to do this:

date_trunc({{duration}}, column)

The duration filter can be set to filter. Then I put a note on the dashboard saying to only enter in year, quarter, month, week or day in the text filter.

I found this works if not a little bit more work on the user's side.

This feels like it would be trivial to add to the application's base function since aggregations add the date_trunc automatically with strings for "month", "day", "week", etc. Why not just add an option to "By Dashboard" and use the {duration} input solution described above?

[paoliniluis]

we're working on this @williamkaper

[ohadw]

@paoliniluis is this part of Metabase 47 by any chance?

[Militao36]

Has this issue already been resolved in Metabase using BigQuery?

[paoliniluis]

@ohadw @Militao36 not yet, it's coming soon

[vinceve]

@paoliniluis is there a timeline on this feature? kind regards

[paoliniluis]

@vinceve we're working on this right now, but we can't provide a timeline unfortunately

[haidary99]

@paoliniluis hey there! Just wondering if this feature is added into Metabase yet? Kind Regards

[paoliniluis]

@haidary99 coming very very soon

[elena-at-spendesk]

Hello! Will it be released soon?

[maximepvrt]

Metabase v50 ? @paoliniluis

[alexBMart]

Hi everyone,

Really interested by that feature. Any idea if it is coming soon?

Cheers.

[kiranjain]

Latest status on this feature request?

[paoliniluis]

It's taking more than expected as we're fixing old bugs

[rjaus]

Having this as a native feature would massively simplify a lot of the dashboards I'm currently working on. Looking forward to the merge.

P.S love Metabase!! Gamechanger. The deeper I go the more impressed I get.

[danjohansenconsulting]

Agreed - we are also anxiously waiting for this to release and my customers really want to see this in-place. Is there a new ETA available?

[vikrant1995]

Hey, any updates on when this will be available? Based on the suggestions above, it's doable for SQL quesitons but then that results in the users being unable to drill down on the charts. Any update on when this will be available for UI Queries? 🙏

[paoliniluis]

Soon

[paoliniluis]

"time grouping" filters should ship in the next major version, but the problem arises when you have SQL and GUI questions in the same dashboard, e.g.: 1) create a SQL question:

SELECT SUM(value), DATE_TRUNC(time_field, {{time_aggregation_field}}) FROM table GROUP BY DATE_TRUNC(time_field, {{time_aggregation_field}})

2) create a GUI question that has a date/time field 3) create a dashboard and add these 2 questions + a time agg filter + a normal category field (add the time aggregations you need to this filter)

As you can't use a normal time aggregation field to the SQL question, you end up with 2 filters