How to create a time frame control for your Looker Studio series chart

This tutorial describing how to create a time frame control has been inspired by a question from the community. This control is strongly associated to the financial world for stock prices charts.

More generally, when building a dashboard or an interactive report, we often tend to add a date selector. I’m convinced that it’s not often necessary, and sometimes, a simple option to expand the period to larger time ranges is sufficient. This is especially true for dashboards aimed at monitoring performance on a fixed time granularity with defined goals and thresholds.

So let’s see how we can build this time frame control for a line chart.

---

---

Ingredients

For this configuration, we will need the following elements:

• Your current data set with at least a date and metric
• Boolean parameters, 4 for my example
• Some calculated fields
• A data blending
• 4 button controls corresponding the parameters
• A line chart and the related chart-level calculated fields

---

Your data set

For this tutorial, I use a BigQuery data source with daily generated leads but you can apply the same with any data set with a date field and a metric:

• data_date
• leads

---

Parameters

To start, let’s create 4 boolean parameters corresponding to the 4 time frames, what will be represented at last through the control bar of the line chart

• “m1” for 1 past rolling month
• “m3” for 3
• “m6” for 6
• “y1” for 12

Here what looks like the configuration of the first one (same value for the parameter name and ID).

---

---

Calculated fields

We need to identify the most recent date in the data set with this simple calculated field

---

max_date

MAX(data_date)

---

Then, we create a second calculated field to return the selected time frame following the value of the 4 parameters.

---

Selected Time Frame

CASE 
WHEN m1 THEN "m1" 
WHEN m3 THEN "m3" 
WHEN m6 THEN "m6" 
WHEN y1 THEN "y1" 
ELSE "m1" 
END

---

Data blending

Now, we configure the blended data “DB – Get the max data of the series” as described below for our example to access to the most recent date of the data set at each data row:

• Left table – Your main data source
  • Dimensions: “data_date”, “Selected Time Frame”
  • Metric: “Leads”
  • (if necessary for your data source) Date range: “data_date”
  • Filter: none
• Right table – Your main data source
  • Dimensions: none
  • Metrics: “max_date”
  • (if necessary for your data source) Date range: data_date
  • Filter: none
• Join configuration
  • Operator: Cross join
  • No key

---

---

The buttons corresponding to the time frame control

Then, the next action consists to add 4 button controls sharing the same styles (here purple), juxtaposed and aligned horizontally:

• The buttons are ordered by time frame and each of them will be fed by one of the parameter
• The label of each button needs to be customized (1M, 3M, 6M, 1Y)
• All the buttons have to share the group 1. Be sure to specify the group 1 for each button: when a button is be clicked, the other buttons of the group will turn to false value
• The first button at the left for the 1 month of time frame is defined as the default value (“Default selection” toggle)

I would advise to use a medium to dark color for the buttons background to optimize the readability regardless of the state of the buttons (clicked with an additional grey overlay or unclicked with the default state).

---

---

Chart configuration

At last, let’s update or add a line chart in our report page with the following configuration(not a time series chart but a line chart to create as series based on the data and not on the selected date range):

• Char type: Line chart
• Setup tab
  • Data source: Your blended data “DB – Get the max data of the series”
  • Dimension: A chart calculated field named “Date ” and corresponding to formula below
  • Metric : A chart calculated field named “Leads” and corresponding to formula below
  • Sort by: Date ascending
  • Default date range: Set to 366 previous days to align it to the widest frame, (see the screenshot below)
• Style tab
  • Series #1 as a line
  • Adapt the other style options

---

Date

CASE 
WHEN Selected Time Frame = "m1" AND data_date >= DATETIME_SUB(max_date, INTERVAL 1 MONTH) THEN data_date 
WHEN Selected Time Frame = "m3" AND data_date >= DATETIME_SUB(max_date, INTERVAL 3 MONTH) THEN data_date 
WHEN Selected Time Frame = "m6" AND data_date >= DATETIME_SUB(max_date, INTERVAL 6 MONTH) THEN data_date 
WHEN Selected Time Frame = "y1" AND data_date >= DATETIME_SUB(max_date, INTERVAL 12 MONTH) THEN data_date 
ELSE NULL END 

---

---

Leads

CASE 
WHEN Selected Time Frame = "m1" AND data_date >= DATETIME_SUB(max_date, INTERVAL 1 MONTH) THEN Leads 
WHEN Selected Time Frame = "m3" AND data_date >= DATETIME_SUB(max_date, INTERVAL 3 MONTH) THEN Leads 
WHEN Selected Time Frame = "m6" AND data_date >= DATETIME_SUB(max_date, INTERVAL 6 MONTH) THEN Leads 
WHEN Selected Time Frame = "y1" AND data_date >= DATETIME_SUB(max_date, INTERVAL 12 MONTH) THEN Leads 
ELSE NULL END 

---

---

---

Configuration review

At last, let’s make a quick configuration review and test the time frame control.

---

---

Alternative for the time frame control: BigQuery custom query

One of the goals of this recipe was to be compatible with any data source. But if your report loads BigQuery data sources, the possibilities will be much broader with a simpler configuration, as usual, relying notably on custom queries. The same principles around the parameters described in my interactive custom date range comparison recipe are used for this case and avoid blending.

Voilà.

---