Working with date dimensions & filters
Last updated
Was this helpful?
Last updated
Was this helpful?
When filtering for the time period you want to analyze, you have 2 options:
Date Range (Custom Period) — This allows you to filter for a specific range or interval using the free-form filter.
Date Range (Preset Period) — This allows you to pick from some preset periods that are more tedious (or impossible) to derive with the free-form date range filter. For example, this enables you to easily filter for the NRF 4-5-4 retail calendar periods.
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. But choosing past 2 complete months would return the previous month, plus the month before that one.
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.
If you filter for a specific date range using the Custom Period date range filter, keep in mind that the end date in the range will not be included in your results.
For example, if you choose 4/1/2025 - 4/30/2025, the results will not include data for the 30th. If you want your results to contain the entire month of April, the date range you should filter for is actually 4/1/2025 - 5/1/2025.
This takes some time to get used to, but one benefit is it doesn't require you to remember how many days are in each month.
Data models that have comparison measures will have 4 versions of the same measure that make it easy to compare recent and historical values, e.g.:
In this example:
Total Net Sales = The Total Net Sales for the period being filtered, i.e. the "current" period
Total Net Sales (Comp) = The Total Net Sales for the comparison period (either the previous period or previous year — more on that below)
Total Net Sales (Change) = Total Net Sales - Total Net Sales (Comp)
Total Net Sales (% Change) = Total Net Sales (Change) / Total Net Sales (Comp)
By default, the comp measures reflect data from the previous period. You can use the Comparison Type parameter to instead reflect data from the previous year:
Comp periods work slightly different depending on whether you are using the Custom or Preset period filters.
If you ever need to understand the exact dates being used for the current and comp periods, bring the Date Comparison Info dimension into the chart. This will give you the start & end dates for the two periods.
Previous Period
Compares the current period to the same-length period immediately preceding it. Current and comp period are always the same number of days. For example, if you filter for the past 30 complete days, the previous 30 days will be the 30-day period immediately before it. Note that if you filter for "last 1 complete months" and the last complete month was April, it will compare 4/1/2025 - 4/30/2025 vs 3/2/2025 - 3/31/2025. This is because the current and comp period are always the same number of days.
Depends on the preset period you've chosen and whether you have customized your calendar. No matter what, it will use the logic specified in your drp.calendar table.
For "to date" periods like "week to date", it will compare to the previous "to date" period. So if you choose week to date, the current period will be for example this week's Sunday-to-Wednesday period, and the previous period will be last week's Sunday-to-Wednesday period. If you choose month to date, it will show for example the 1st to the 15th of this month vs. the 1st to the 15th from last month.
For the "latest" periods like "latest month", it will compare the most recent complete month vs the preceding complete month.
Unlike the Custom Period logic, there may not always be the same number of days in the current and comparison period.
Previous Year
Compares the current period to the same period 1 year ago. To derive the comp period start and end dates, we subtract one year from the current period start and end dates.
Depends on the preset period you've chosen and whether you have customized your calendar. No matter what, it will use the logic specified in your drp.calendar table.
For "month", "year", and "quarter" periods, it will compare to the same dates minus 1 year.
For "week" periods it will compare to the same period 52 weeks ago (so that the current and previous periods start and end on the same day of week).
For "retail" periods like "Latest Retail Month" it will compare the most recent retail period vs the same retail period of the previous retail year.
Previous Year (Same day of week)
Compares the current period to the same period 52 weeks ago. To derive the comp period start and end dates, we subtract 52 weeks from the current period start and end dates. This ensures that the current and comp periods always start on the same day of week.
Results will be the same as the Previous Year comparison. Please speak with our agency team if you want to customize this.
If you want to include both the current period date and the comparison period date in the tooltip of a chart, toggle on the Show Comparison Date (Yes/No) parameter. This lets you easily see which dates are being compared.
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:
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.
(Click to enlarge)
