Advanced controls

Google Data Studio – Time dimension slider to adapt granularity of series chart

March 17, 2021 - by Mehdi Oudjida
Google Data Studio - Time dimensions slider for series chart

I’m really disappointed when dashboards – made for weekly or monthly data – are used with a date range of one day, especially when we talk about time series charts and related trends…disappearing…and becoming a kind of simple score card, lost in the space.

The opposite is also true: A time series chart with plots and labels is easily overloaded if the selected date range is too large.

So, let’s try to provide some controls on time series chart to change the time dimension for greater clarity or more time granularity, switching as example from daily plots to hours if we select only one day of data.

It is an opportunity to use the amazing parameter feature. Comparing to my article about primary and secondary dimension selection, we will use the same kind of mechanism to select dimension through parameter. but this time, we will play with slider control and date formats.

CASE statement enhancements thanks to the release of March 11, 2021

For this new version of the article about time dimension slider (initially published in September 2020) – we will take advantage of the last release of Google Data Studio.

Last Thursday, “CASE” statement has been enhanced to allow advanced expressions for conditions and output, like using alteration and calculation functions or for easier parameter usages.

These welcomed improvements reduce dramatically the number of required calculated fields for this kind of custom control.

The first version of this article required 7 calculated fields Vs 2 for the version described below:

  • No more intermediary fields only used in one case statement
  • No more – for this case – parameter wrapped in a calculated field to use it in condition expressions

The result

Let’s see this new combination of Data Studio built-in features, customized to address a specific viz requirement for interactive reader experience.

Ingredients to create the mechanism of time dimensions slider

To get this time dimension slider, we will use the following native features provided by Google Data Studio:

  • Data source parameter
    We will use it to store our 4 levels of time data
  • Calculated fields
    To wrap the parameter as a calculated field and to map the selected option of the parameter with the related dimension to display (meta data to data)
  • Slider control
    for the dynamic selection of time dimensions, from the smaller (hour) to the bigger (month)
  • Line chart
    Finally, to display our data, we use line chart with time dimension as text to be able to switch format a level to another without be limited by date format

Data source parameter

Let’s create a parameter inside our data source to store the options we wish to propose through the slider. In this example I use a Google Analytics data source providing me Hour and Date basic dimension. I will use them in the next steps.

Time dimension slider is a parameter as a range of integers, from 1 to 4. Sliders can only be used with numeric values. So we need to create a parameter based on integers, then map each integer with the right dimension.

  • 1 for Hour
  • 2 for Day (default value)
  • 3 for Week
  • 4 for Month
Google Data Studio - Time dimensions slider - Parameter

Calculated fields

Let’s build required calculated fields. I use “TDS” as prefix for “Time dimensions slider”.

First, we need a calculated field to display a dimension label at the left of the slider. We plan to put some icons at the top of the slider, but let’s give also this information with a clear text. So for each value of the parameter we map a time granularity label.

TDS - Time dimension label
CASE 
 WHEN Time dimension slider  = 1 THEN "Hour"
 WHEN Time dimension slider  = 2 THEN "Day"
 WHEN Time dimension slider  = 3 THEN "Week"
 WHEN Time dimension slider  = 4 THEN "Month"
 ELSE "Day"
 END

Then, we create the most important dimension. It adapts the format of the date, following the selected granularity.

To avoid format incompatibility, we choose text output for the 4 cases. You can create this CASE statement using directly the parameter value, or like the example below, using the label, for a better readability.

TDS - Selected time dimension
CASE
  WHEN TDS - Time dimension label = "Hour" THEN RIGHT_TEXT(CONCAT("0",CAST(Hour AS TEXT)),2)
  WHEN TDS - Time dimension label = "Day" THEN CAST(TODATE(Date, "%Y-%m-%d") AS TEXT)
  WHEN TDS - Time dimension label = "Week" THEN CAST(TODATE(Date,"%Y W%W") AS TEXT)
  WHEN TDS - Time dimension label = "Month" THEN CAST(TODATE(Date, "%Y-%m") AS TEXT)
  END

Slider control and related label

Now the configuration of the data source is finished, we add the slider and we select the parameter Time dimension slider as control field (Data tab) and adapt styles. We must also define 1 for the step size in the snapping options.
We can also decorate it with 4 icons located on the 4 possible static positions of the slider value.

Then, we add at the left of the slider, a table chart with one line, displaying TDS – Time dimension label dimension without metrics and with a light style (contextual info)

Line chart

Our last action is the change of the time series chart to a line chart with:

  • TDS – Selected time dimension as primary dimension
  • Here, Sessions as metric
  • Sorting by TDS – Selected time dimension ASC to respect the time series sorting
  • Adapted other configurations and styles

Then, we test the final result and the interaction between the slider and the displayed data.

Voilà! It’s ready

Let’s review the full configuration with the video below.

Other option: Automatic adaptation of granularity

We can also imagine to insert an automatic system of granularity selection following the number of days covered by the date range selection, like it is described in the article about adaptative sparkline.

In this case, the automatic selection will take place at the value 0 of the time dimension parameter and we will use blended data. At the end, the dimension used but the line chart will be directly declare as a chart calculated fields with the following formula.

CASE 
  WHEN TDS - Time dimension extended label != "Auto" THEN TDS - Selected time dimension
  WHEN TDS - Number of days > 150 THEN CAST(TODATE(Date, "%Y-%m") AS TEXT)
  WHEN TDS - Number of days > 21 THEN CAST(TODATE(Date,"%Y W%W") AS TEXT)
  WHEN TDS - Number of days > 1 THEN CAST(TODATE(Date, "%Y-%m-%d") AS TEXT)
  WHEN TDS - Number of days <= 1 THEN RIGHT_TEXT(CONCAT("0",CAST(Hour AS TEXT)),2)
  END

Demo report

If you need to play with this custom slider, you can test the following Google Data Studio report, changing the date range and the time granularity of the series chart. Cheers!

Google Data Studio demo report: Time dimensions slider for series chart

De temps en temps…