Open navigation

How to fix errors with blank comparison period values in Google Sheets queries

If you split and compare data by a dimension that doesn’t exist in both of the time periods in the comparison, the fields returned will be blank. 

This happens because the query can’t compare a set of values to blank results, which is what will be returned if the query includes a dimension that wasn’t present in either of the time periods being compared to each other.


Most queries

The simplest way to fix this is to remove the dimension that is not shared in both comparison ranges (i.e., remove the date/time dimension).

Queries that depend on data being shown over time

However, if your report depends on the data being shown over time, removing the missing dimension won’t solve the problem. 

Instead, run a query on a separate tab for the full timespan, including what will be the comparison period. This is the raw data. Then in another tab (the report), use standard spreadsheet functionality (references, vlookups, math functions, for example) to build out the report and calculate the comparisons from the earlier data in the raw dataset.

Setup example for changes over each week

Say you want to see 4 weeks in your report and see the change in between weeks. First, fetch data for Last five weeks to sheet "Data". The numbers will be in columns B, C, D, E, and F. Then add another sheet and rename it to "Report" and link the data so that you get the metric names to column A (formula is '=Data!A2' for column A2 in Report, etc.). Now you want to get the original numbers, so reference the values in Data to Report so that data in column B goes to B, C => D, D => F, E => H, F => J.

Then create the comparison calculations in between the now existing numbers in sheet Report. Then hide columns B and C (this is why we wanted to fetch five weeks of data to get comparison data for all four weeks) and you're done.

The formula for column E1 is '="Change from "&B1'. Now if you set your report ("Data") to update every week, you have an up-to-date report waiting for you when ever you choose to view the report.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.