I’m starting with Google Data Studio. So I have this
event_date (YYYYMMDD) and
event_timestamp (Unix Epoch in microseconds) columns for each record from BigQuery (original data source is Firebase). I created a time-series / bar chart (I tried both) that displays the x-axis as the hour of the current day (I filtered on
event_date) And the y-axis as the number of records.
The way I did this is to create a calculated field using the Data Studio function
HOUR(event_timestamp) and then displayed as
HH. The problem here is for certain hours that we don’t have any corresponding record, there is no entry for the x-axis. What I want is even if there are no records for that specific hour, it will still show. So basically, the x-axis numbers look like this:
00, 01, 02, ..., 22, 23
Side question: I noticed that the time series chart sorts from most recent to oldest. How can I reverse the sort?
I can think of a couple ways to do so that basically rely on an
OUTER JOINWith all possible dimension values (0-23h).
To frame the problem I will use a BigQuery public dataset:
TIMESTAMPA field containing the time when each ride started. I will filter out all trips that started before 7 AM with the following query:
Therefore, I get the same problem when creating a data source with the previous custom query (hours without data are not displayed):
Blending with range data
The first and quick fix would be to create a data source that contains all possible dimension values. This could be done in a spreadsheet or, for example, with an additional 0 B custom query:
and now we blend the data so that the hour array is on the left side (Data Studio uses
LEFT OUTER JOIN) and join with
Then we get the desired histogram (be sure to change the number of bars up to 24 in the
STYLEtab and sort by
hourascending in the
Aggregate in BigQuery
Another alternative would be to run the calculations for the hourly buckets already in the custom query. For example, in this case, we would generate the hour array, left outer join it with the aggregated data and substitute
Which gives the same result.