How to configure a time granularity slider and control the date dimension of your Looker Studio time series charts

---

Why building a time granularity slider? I’m really disappointed when Looker Studio reports – 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.

---

The result

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

---

---

Ingredients to create the mechanism of the time granularity slider

To get this time dimension slider, we will use the following native features provided by Looker 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 BigQuery tables as my 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

---

---

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 right 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, Orders 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.

---

Configuration review of the time granularity slider

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

---

De temps en temps…