Google Data Studio: filtering times to the past hour (you’d think it’d be easy)

I am within 3 weeks of first meeting Google Data Studio (DS). I am head of data at a small firm that was subsumed into a larger firm last year. Now that the initial merge issues are clearing, we’re getting down to the brass tacks of making our very different technologies work together. They’re very google-oriented – lots of BigQuery & Go and the data bods use R. I’m more MySQL and Python myself. We didn’t have a regular BI tool, hence why I’m getting up to speed with DS atm. DS gives a lot of bang for your buck (it’s free!), but has some interesting quirks.

I’ve had a few other battles with DS already, hopefully I’ll get to expand on those in further articles. Some of these are real head-scratchers; things you would think are such obvious requirements that the lack of them certainly makes me think my dearth of experience is the issue. Such a one seems to be the inability to filter values by time. An initial internet search turned up a couple of hits, but nothing really concrete:


So! How I can filter a set of timestamped results to show only values from the past hour?

This is the data I’m working with: the first column is a timestamp incrementing every minute and second column is an integer.

I’ve inserted a Chart > Scorecard and I want to sum up the values in column 2 if the time in column 1 falls within the past hour.

Data Studio Scorecard in edit mode

DS has grand tools if I want to filter by the date:

The image shows a configurable date picker – showing is a fixed start date of Jan 1st 2020 and an end date of now.

But it seems only to let you set the date, the time of day is not adjustable. Minimum “Unit” granularity is “day”; even when my data type is Date & Time or Date Hour Minute.

Okay, so now what? I’ll try to make a different kind of filter – based on the time value

A screenshot of a filter creation from DS

So, it starts out looking hopeful, I can choose the time metric and only include times that are later than a date and time I pick! But on closer inspection, that’s a fixed time, not a relative one, so no dice. Clicking around, nothing else in the filter allows relative comparison, so I’ll have to think of something else.

Hmmm. What about if I set an is_in_past_hour boolean? I’ll make a new calculated field. Oh, and by the way, DS doesn’t do IF statements. It’s case or nuffink.

My first attempt – apparently this is hideous. Have you seen all the limitations of case statements?

After a bit of jiggery-pokery, including

case when datetime_diff(current_datetime(),the_time,HOUR)=0 then true else false end

erroring with Invalid formula – Invalid input expression. – WHEN conditions must compare a dimension or metric with a literal value.

I ended up ditching the case statement altogether and made an hours_before_now variable instead. I will filter results so that only results where this variable is 0 are included.

datetime_diff(current_datetime(),the_time,HOUR)

Filtering the hours_before_now variable to 0

Phew. All done. But hang on, why are the results still not matching what I’m expecting?

Rounding.

It’s rounding half hours up or down.

Le sigh.

So I switch all the references in my filters and variables from hours to minutes and check there are fewer than 60 of them.

Change the variable to use minutes
Change the filter to allow < 60 minutes

Finally, this works. I’m getting the same value in the DS report as this simple wee query in MySQL:

select sum(my_value) 
from my_table 
where the_time >= date_add(now(), interval -1 hour)
;

Time (out) for a coffee.

StuffGoesWrongException handling for today

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s