Working with Date Dimensions
Last updated
Last updated
When filtering on a calendar dimension, Looker will give you the option to filter, for example, the "past 7 days" or the "past 7 complete days".
Choosing between these depends on whether you want the current unit of time included in your analysis. So if you want the current day to be included, you would choose "past 7 days". This would filter your results to display the current day, plus the previous 6 complete days. If you don't want the current day to be included, you would choose "past 7 complete days."
The same applies for other timeframes. So if you choose the "past 2 months", it will return results from the previous month, plus the current month.
Most of our data models only include data through the previous day. So if you're filtering based on a number of days, typically you will want to filter for complete days to avoid including days with only partial data.
Something to keep in mind when filtering for specific date ranges is that the results will not include the end date. So for example if your date range is 10/1/2023 - 10/31/2023, the results will not include data for the 31st. If you want your results to contain the entire month of October, the date range you should filter for is 10/1/2023 - 11/1/2023.
💡One benefit of this is it doesn't require you to remember how many days are in each month.
There is a set of "Dynamic Date Type" fields in the calendar view that will allow you easily switch the units of time displayed in a visualization my toggling a filter:
To use this feature, you would add the Dynamic Date Type Picker filter and then add the Dynamic Date Type dimension to your query:
A lot of times you'll want to compare data from two different timeframes to see how performance has changed. You may want to compare year-to-date results vs the previous year-to-date results, or you may want to compare the past 30 complete days vs the previous 30 complete days.
We have sets of dimensions built in to Looker to help you do this.
"Compare to Previous" dimensions help you segment your analyses similar to how you can segment them in Google Analytics.
These dimensions allow you to choose a date range and compare the results to either the previous period or the same date range in the previous year.
To compare timeframes using these dimensions, you would specify the period for comparison using the "This Period" filter field. Then add the Comparison Periods dimension to the table. This dimension will include values for:
This Period
The date range you specified in the filter
Previous Period
The timeframe immediately prior to the "This Period" date range. This will be the same number of days as the "This Period" date range.
Previous Year
The same date range as the "This Period" date range, but 1 year ago.
null
All dates that fall outside of the other 3 date ranges
You can filter the results using the "Comparison Periods" filter to compare to the previous period only or the previous year only, for example:
When using these dimensions, you should always at least filter the "Comparison Periods" to exclude NULL values. This will ensure that only results from one of the comparison periods will be included in your analysis:
(Click image to enlarge)
"Date Pivots" dimensions are a little more robust than the "Compare to Previous" dimensions. They let you take a date range and allow you to compare it in the following ways:
Pivot by Today
Today vs. Yesterday vs. Same day last year
Pivot by Yesterday
Yesterday vs. Previous day vs. Same day last year
Pivot by Retail Week
This week vs. Last week vs. This week last year (Comparison is based on the 4-5-4 NRF retail calendar)
Pivot by Month
This month vs. Last month vs. The same month last year
Pivot by Retail Month
This month vs. Last month vs. This month last year (Comparison is based on the 4-5-4 NRF retail calendar)
Pivot by Quarter
This quarter vs. Last quarter vs. The same quarter last year
Pivot by Year
This year vs. Last year
A useful feature of these dimensions is they will only compare apples to apples time periods. This makes it easy to ensure you're not comparing partial timefames to full timeframes.
For example, if you're only 15 days into the month and use the "Pivot by Month" dimension, the results returned will only be for the first 15 days of this month, last month, and the same month last year. Similarly, if you are 200 days into the year and use the "Pivot by Year" dimension, it will only return data for the first 200 days of this year and the first 200 days of the previous year.
When using these dimensions, you should always filter out NULL values. This will ensure that only results from one of the comparison periods will be included in your analysis.
Use "Compare to Previous" fields to compare the past 90 days vs the 90 days immediately before that period:
Use "Compare to Previous" fields to compare an arbitrary date range vs. the same date range last year like 9/16/2023 - 9/23/2023 vs. 9/16/2022 - 9/23/2022:
Use the "Pivot by Month" Date Pivots dimension to compare month-to-date performance vs the same month-to-date timeframe in the previous month. (We've included a count of calendar date to emphasize that these periods are the same length):
Use the "Pivot by Yesterday" Date Pivots dimension to compare yesterday's performance vs the same day last week (e.g.: This Wednesday vs Last Wednesday):
Sales are different every day of the week due to varying dates between different months and years.
This makes it difficult to compare year over year sales until the 1940s, when the use of a retail calendar became common.
The retail calendar allows you to compare sales across different time periods to help measure performance in a more dynamic fashion, and potentially predict sales based on historical trends. Retail calendars are constructed with four quarters with 91 days in each, in either a 4-5-4 or a 4-4-4 format (i.e. a month with 4 weeks followed by a month with 5 weeks and finally a month with 4 weeks). This ensures that every month has the same number of weekends as the same month in the prior year, so that sales for the same comparable time period have the same number of weekdays and weekends. Additionally, the National Retail Federation (NRF) uses a 4-5-4 calendar and adjusts the start of the calendar year to ensure that major holidays are reflected in the same time period for proper comparisons.
The Daasity retail calendar (shown as the "Calendar" view in most explores) follows the NRF calendar dates shown in the link above. Along with the NRF calendar structure, this view contains a number of pivot dimensions that allow you to compare data from different timeframes.
