Why is data missing in a time series chart when using a blend?

user_18470785 0
Viewed 232

My data comes from 2 Google Sheets (links below). When putting it in time series chart it shows all data as expected (I am using linear interpolation and set the time to Date Hour). When doing the same with a blend (same data exactly using keys from another sheet), I get one dot of the data with Date Hour and a some more points of data when using Date Hour Minute, but still missing the most of the data.

Not blended data (wanted output):

Not blended data(wanted output)

Blended with Date Hour:

Blended with Date Hour

Blended with Date Hour Minute:

Blended with Date Hour Minute

Data Set 1 - Google Sheets with keys:

deviceId Room Size
39 122 M
40 122 L
42 1 L
43 2 S

Data Set 2 - Google Sheets with input data (first 9 rows shown; link contains 742 rows):

deviceId updatedAt soilMoisture
40 2022-06-16T12:55:39.185Z 502.49
40 2022-06-16T11:55:59.733Z 472.37
40 2022-06-16T10:56:00.597Z 457.96
40 2022-06-16T09:56:05.304Z 479.84
40 2022-06-16T08:56:14.428Z 452.59
40 2022-06-16T07:56:43.934Z 490.74
40 2022-06-16T06:57:16.305Z 488
40 2022-06-16T05:57:09.134Z 446.17
40 2022-06-16T04:57:35.437Z 483.73

Google Data Studio Report

google-data-studio time-series
1 Answers

Issue

Upon further testing of the chart in the blend, it seems that the issue is that not all the rows of data are displayed in the time series when setting the Date & Time granularity Date Hour Minute or Date Hour; when setting the granularity to Date & Time the following error was displayed:

Too Many Rows
Due to the number of rows, the chart cannot be rendered.
Sorry, please try other metrics or dimensions with fewer rows, try a different data source, or add a filter.

A defect report titled Time Series Date Error - Too Many Rows: Due to the number of rows, the chart cannot be rendered was created on 19 Jun 2022 in the Google Data Studio issue tracker, that elaborates on the issue.

Suggestion

One workaround is to convert the existing time series chart to a line chart:

Setup Tab

  • Dimension: updatedAt; Type: Date & Time (set based on the required granularity - Date Hour Minute, Date Hour, Month, etc )
  • Metric: soilMoisture; Aggregation: AVG
  • Sort: updatedAt; Order: Ascending
  • Secondary Sort: soilMoisture; Order: Descending

Style Tab

  • Number of Points: 5000 (Set to 500 by default; tentatively set to 5,000; change as required. In this case (as each date time value in the data set is unique) each point represents one row of data, thus as there are 742 rows, ensure that it's set to at least 742 to view all the data)

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

gif_v2

user_128925530
Related