Search code examples
looker-studio

Data Studio Date Range Filter on hour, minute level

I am preparing a DataStudio report that reads data from BigQuery. It is a timeseries chart. I have applied date range filter. The date range filter gives filtering on date level. However, I need the range on hour/minute level. e.g. if i set date range filter from 18 march to 19 march, it will show me data on 18 march. However, I want to see data on 18 March between say 1700 to 1800. The date range filter is based on timestamp column in BigQuery table. How to do this, I don't see any documentation and/or option to set in date range filter.

Solution

  • Updated the post with a Google Data Studio Report to demonstrate a 3 Step Process (each with a GIF to visualise):

    Step 1: Create a Date Hour Minute (YYYYMMDDhhmm) Field

    Changing the Type of the DateTime field (at the Data Source) from the default YYYYMMDD to Date Hour Minute YYYYMMDDhhmm should do the trick (add the YYYYMMDDhhmm field as the primary dimension in the respective chart).

    Note: As best practice, ensure that there is a YYYYMMDD date field (if there is just a single Date field, then duplicate it).

    GIF to elaborate on Step 1:

    2]

    Step 2: Charts

    There are a couple of chart types to consider:
    2.1) Time Series Chart
    - Dimension: DateTime

    2.2) Line Chart
    - Dimension: TODATE(DateTime, "%H:%M")
    - Sort: TODATE(DateTime, "%H:%M") in Ascending order

    GIF that details Step 2:

    5]

    Step 3: Filter Controls

    Create two Filter Controls, one for the Start DateTime that uses the comparison >= operator, and the second for End DateTime that utilises the <= comparison operator, both created using the following:
    - Dimension: CAST(DateTime AS NUMBER )
    - Style: Search All
    - NOTE: When using a Time Series Chart with the two Filter Controls, ensure that the Date Range Control is set to Auto Date Range
    - Use Case (to display values between 17:00 and 18:00 on the 18th of March 2020):
    Start Date >= 202003181700
    End Date <= 202003181800

    GIF that visualises Step 3:

    8]