I have a GitHub commit database for one repository and have built a bar chart showing the commit count for each day.
Is it possible to make the bar chart responsive so that when the date range exceeds one month, it automatically displays commits weekly instead of daily?
Hey,
Hope you’re keeping well.
Looker visualizations don’t automatically change granularity based on the selected date range, but you can achieve this behavior using a dynamic dimension and parameter logic in your LookML model. Define a parameter for date range length and use a case statement in a derived table or SQL dimension that switches between DATE_TRUNC(${created_date}, WEEK) and DATE_TRUNC(${created_date}, DAY) depending on the range selected. The query can reference a filter-only field or custom dimension that evaluates the number of days between start and end dates to determine whether to aggregate by day or week.
If you are using Looker Studio, there’s built-in support for date granularity controls that let viewers toggle between day, week, or month groupings. In that case, you can configure the chart’s Date Range Dimension to “Auto” and enable the viewer control, which adapts the x-axis grouping based on the selected range. This approach keeps the visualization responsive without modifying the underlying model.
Thanks and regards,
Taz
I use Looker Studio, and the URL you provided does not work for me.
Could you explain how to do this in more detail?
Hey Daniel,
It seems Mim’s article at his datamonkeysite.com blog exactly explain how to achieve it in Looker Studio:
In BQ Custom SQL you add additional columns for granularities you need + special column for difference in days.
CAST(DATE_TRUNC(DATE( date_day ), MONTH) AS timestamp) AS month,
CAST(DATE_TRUNC(DATE( date_day ), WEEK) AS timestamp) AS week,
DATE_DIFF(
PARSE_DATE('%Y%m%d',@DS_END_DATE),
PARSE_DATE('%Y%m%d',@DS_START_DATE)
,DAY) + 1 AS nbrdays
then nbrdays column is used in case statement to againt <=7 or <=31 to choose the right granularity to be displayed later in a chart.
Here a tutorial with one of the solution.
This one has the benefit to be applicable for any data source type.
The automatic adaptation of the granularity is at the end of the article.
https://www.withlookerstudio.com/blog/advanced-controls/20210317-time-granularity-slider-for-looker-studio-date-series-charts/
I hope it helps.