Skip to content

date_sub_isoyear

date_sub_isoyear(date, years)

Description

Returns same day years before (same week number and same day of week)

Usage

Call or Deploy date_sub_isoyear ?
Call date_sub_isoyear directly

The easiest way to use bigfunctions

  • date_sub_isoyear function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy date_sub_isoyear in your project

Why deploy?

  • You may prefer to deploy date_sub_isoyear in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

date_sub_isoyear function can be deployed with:

pip install bigfunctions
bigfun get date_sub_isoyear
bigfun deploy date_sub_isoyear

Examples

select bigfunctions.eu.date_sub_isoyear(2023-06-02, 3)

select bigfunctions.us.date_sub_isoyear(2023-06-02, 3)

select bigfunctions.europe_west1.date_sub_isoyear(2023-06-02, 3)

+------------------+
| substracted_date |
+------------------+
| 2020-05-29       |
+------------------+

Use cases

A use case for the date_sub_isoyear function would be analyzing year-over-year performance based on ISO week alignment. Imagine you have sales data and want to compare the sales of a specific ISO week in the current year to the same ISO week in previous years.

Scenario: A retail company wants to compare sales performance of the first week of June 2023 (ISO week 22) to the sales of the same ISO week (week 22) in 2022 and 2021.

Query Example (using the europe-west1 dataset):

WITH SalesData AS (
    SELECT
        DATE('2021-05-31') AS sale_date, 1200 AS sales_amount UNION ALL  -- Example data for week 22 in 2021
    SELECT
        DATE('2022-05-30') AS sale_date, 1500 AS sales_amount UNION ALL  -- Example data for week 22 in 2022
    SELECT
        DATE('2023-06-05') AS sale_date, 1800 AS sales_amount           -- Example data for week 22 in 2023
)

SELECT
    sd.sale_date,
    sd.sales_amount,
    bigfunctions.europe_west1.date_sub_isoyear(sd.sale_date, 1) AS previous_year_date,  -- Date of the same ISO week in the previous year
    bigfunctions.europe_west1.date_sub_isoyear(sd.sale_date, 2) AS two_years_ago_date    -- Date of the same ISO week two years ago
FROM
    SalesData sd
ORDER BY sd.sale_date;

This query would return a table showing the sales for each date, along with the corresponding dates in the previous two years that fall within the same ISO week. This allows for direct comparison of sales figures across consistent ISO weeks, regardless of calendar date shifts.

Other potential use cases:

  • Financial reporting: Comparing financial performance across ISO years.
  • Marketing analysis: Tracking campaign effectiveness based on ISO week alignment.
  • Supply chain management: Analyzing inventory levels and demand based on consistent ISO week patterns.
  • Any time series analysis where comparison across ISO weeks is more relevant than calendar dates.

By using date_sub_isoyear, you ensure that comparisons are made across equivalent time periods in different years, according to the ISO week numbering system, providing a more accurate and meaningful analysis.

Need help or Found a bug?
Get help using date_sub_isoyear

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about date_sub_isoyear

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on