CODEWITHSUNDEEP
looker-studio
ThiagoAlves
ThiagoAlves

Reputation: 898

Google Data Studio: don't show null values in a time series chart

I have a time series chart with 4 cumulative metrics. The blue one represents the actual month-to-date data, and the other 3 lines represents the expected value and the control ranges for the metric represented in blue, until the end of the month:

current scenario

I would like to have a chart like this instead (displaying only the actual data, from the first day of the month until today):

desired scenario

Here's a sample of my data source (query being executed directly in the PostgreSQL database). The "hours" column represents the blue dotted line in the chart. Note the NULL values after Jun 25:

original data source

When I try to visualize the data in the Explorer, this is what I get instead (zeros instead of NULLs):

data being retrieved from the source

I wonder if it is possible to skip the NULL values from the cumulative metric (the blue line), while still showing the other 3 lines until the end of the month. Maybe it has to do with the NULL data being converted to zero when it is retrieved by the data source?

Any insights or ideas are welcome.

Upvotes: 4

Views: 3164

Answers (1)

Diego Queiroz
Diego Queiroz

Reputation: 3411

Since the data is from a PostgreSQL data source, you can achieve what you want if you calculate the running sum directly on it, instead of using Data Studio to calculate it.

It should be something like:

SELECT
  *                                 -- your fields
  , case
      when hours is NULL then NULL  -- keep null values as null
      else sum(hours)               -- cumulate non-null values
           over (order by day_of_month asc rows between unbounded preceding and current row)
    end as hours_cumulated
FROM your_table

Upvotes: 2

Related Questions