How to Create Custom Date Range Presets

You can create custom date ranges for specific use cases such as wanting to leverage a 4-5-4 calendar by setting up a dedicated dataset and linking it to a dropdown filter. This approach allows you to define specific date periods that match your retail calendar.

Creating the Date Range Dataset

First, create a new dataset with your custom date ranges. Here's a sample BigQuery SQL query that generates weeks based on the retail calendar:

-- 1. Determine the fiscal start date (Monday of the week containing Feb 1)
WITH raw_feb_1 AS (
  SELECT DATE_FROM_UNIX_DATE(UNIX_DATE(DATE(EXTRACT(YEAR FROM CURRENT_DATE), 2, 1))) AS feb_1
),
fiscal_start AS (
  SELECT 
    CASE 
      WHEN EXTRACT(DAYOFWEEK FROM feb_1) = 1 THEN DATE_ADD(feb_1, INTERVAL 1 DAY)
      ELSE DATE_SUB(feb_1, INTERVAL (EXTRACT(DAYOFWEEK FROM feb_1) - 2) DAY)
    END AS start_date
  FROM raw_feb_1
),
weeks AS (
  SELECT 
    DATE_ADD(start_date, INTERVAL 7 * week_num DAY) AS week_start,
    DATE_ADD(DATE_ADD(start_date, INTERVAL 7 * week_num DAY), INTERVAL 4 DAY) AS week_end,
    week_num + 1 AS fiscal_week
  FROM fiscal_start, UNNEST(GENERATE_ARRAY(0, 51)) AS week_num
),
week_ranges AS (
  SELECT 
    FORMAT_DATE('%m/%d/%Y', week_start) || ' - ' || FORMAT_DATE('%m/%d/%Y', week_end) AS week_range,
    week_start AS start_date,
    week_end AS end_date,
    fiscal_week
  FROM weeks
)
SELECT *
FROM week_ranges
ORDER BY fiscal_week;

This query will generate a list of date ranges in the format "MM/DD/YYYY - MM/DD/YYYY".

Using the Custom Date Ranges in Reports

Once you have your dataset with custom date ranges:

1. Create a dropdown filter that uses this dataset

2. In your main query, reference the selected date range using a query like this (BigQuery example):

SELECT 
    * 
FROM
    Ecommerce.all_orders
WHERE 1=1
    [[
    AND
      DATE(shipping_limit_date) BETWEEN 
        PARSE_DATE('%m/%d/%Y', SPLIT({{select_3}}, ' - ')[OFFSET(0)]) AND 
        PARSE_DATE('%m/%d/%Y', SPLIT({{select_3}}, ' - ')[OFFSET(1)])
    ]]