How to make date comparisons queries with time dimensions
As you may know, in Dataslayer’s extension for Google Sheets you can make time comparisons. You can use this function from the "Dates" menu:
If you are going to make comparisons with time dimensions, keep in mind that the dimension must always be of a lower rank, for example: if your time range is “last month” and you put "month" as a time dimension, it is not going to bring data.
This is because comparisons can only be made between columns in the same time range and with the same name (for example, you can compare "January of 2022” with "January of 2021”), but you cannot compare "January 2022” with “February 2022" with a month dimension. Only if you use a time dimension like week or date, it will bring data correctly.
When making these type of comparisons, we recommend to not add any time dimensions, as the time range can be configured in the comparison itself. That way you make sure the comparison query will work fine. But, if you still want to use them, the following solution might help you:
Make two different tables, one with the past period to compare and other with an automatic refresh with the current period, so as the time period elapses, the queries can be compared in real-time.
In addition, you can make these comparisons automatically and more visual by using Google Sheets formulas.
If you are going to make comparisons with time dimensions, keep in mind that the dimension must always be of a lower rank, for example: if your time range is “last month” and you put "month" as a time dimension, it is not going to bring data.
This is because comparisons can only be made between columns in the same time range and with the same name (for example, you can compare "January of 2022” with "January of 2021”), but you cannot compare "January 2022” with “February 2022" with a month dimension. Only if you use a time dimension like week or date, it will bring data correctly.
When making these type of comparisons, we recommend to not add any time dimensions, as the time range can be configured in the comparison itself. That way you make sure the comparison query will work fine. But, if you still want to use them, the following solution might help you:
Make two different tables, one with the past period to compare and other with an automatic refresh with the current period, so as the time period elapses, the queries can be compared in real-time.
In addition, you can make these comparisons automatically and more visual by using Google Sheets formulas.
Updated on: 07/10/2022
Thank you!