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.


For example, if you select the Month dimension and try to compare data from September to its previous month (August), no comparison data will be shown because the Month value September does not exist in August's data. 


Instructions 

Most queries

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


Queries that include Date dimensions

When comparing data while using date dimensions, this can result in blank comparison values since the exact same date does not exist in both fields. 


In the example of comparing September data with August data using the Month dimension, no comparison data will be shown because the Month value September does not exist in August's data.


However, if we use the date dimension Day Of Week, and request the same data from September and August to compare, the comparison values will show because the date values (Monday, Tuesday, Wednesday, and so on) exist in both September and August.


To avoid this issue of blank values, make sure that the exact same date dimension values exist in both the primary metric and the comparison metric.



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.