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.
Issues with leap years
Comparing data for leap years can cause some issues. For example, comparing data for February 29, 2024 to last year's data for the same day will try to compare the data to February 29, 2023, which doesn't exist. So, the comparison will instead take the next available date, March 1, 2023, and use that as the comparison data. The comparison will then continue with this offset, meaning that March 1, 2024 will compare to March 2, 2023, and so on.
This also means that the last date of the selected range will be empty/zero as the date it wants to compare to is not in the data set. For example, if the end date of the query is March 11, 2024, it would try to find March 12, 2023, but this isn't in the date range as it would stop at March 11, 2023. This means that the comparison value returns as empty per the known issue described further up the article.
We're hoping to find a solution to this issue soon.