Why Are My Comparison Period Values Blank?

The comparison period feature does not work properly if your data is split by something not found in both time periods, as it cannot make the comparison to non-existent items. In these situations, it will return no value for the comparison fields.

Demonstrating the Issue

A very common configuration that leads to this situation is splitting by a date/time dimension with the "Previous period of same length" comparison. For example, if you were looking at January 2018 to June 2018 as your date range, had the query split by "Year & month" and then enabled the "Previous period of same length" comparison setting, it would give you empty values for the comparison, even if there is data available for the comparative time period.

Example showing that the 'Change %' column for the comparison is blank when splitting the query by a date/time dimension

So Why is This Happening? 

In the example above, the issue is that the dates in January 2018 to June 2018 do not exist in the comparison period of July 2017 to December 2017. As the dates don't exist to compare to given the range, then the values are truly blank. This is the correct response from the system in this situation. Note though, that the comparison period of "Same dates a year ago" DOES work with this setup and this is because we formulate what that comparison date range is by subtracting a year.

Working Around the Issue

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

Example showing the 'Change %' column now populates without the "Month" column splitting

However, this doesn't work if your report depends on the data being shown over time! What you can do instead then is 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 that is the report, you can use standard spreadsheet functionality (references, vlookups, math functions, etc.) to build out the report and calculate the comparisons from the earlier data in the raw data set.

Here is a more detailed setup example for a user that wanted to show changes over each week:

Say you want to see four 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.

Here's a picture as an example of how this was 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 t the report.

Example showing week-over-week comparisons

Did you find it helpful? Yes No

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