bigfunctions > date_sub_isoyear

date_sub_isoyear¶

Call or Deploy date_sub_isoyear ?

✅ You can call this date_sub_isoyear bigfunction directly from your Google Cloud Project (no install required).

This date_sub_isoyear function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
You may prefer to deploy the BigFunction in your own project if you want 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). Discover the framework

Public BigFunctions Datasets:

Region	Dataset
`eu`	`bigfunctions.eu`
`us`	`bigfunctions.us`
`europe-west1`	`bigfunctions.europe_west1`
`asia-east1`	`bigfunctions.asia_east1`
...	...

Description¶

Signature

date_sub_isoyear(date, years)

Description

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

Examples¶

EUUSeurope-west1

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       |
+------------------+

Need help using date_sub_isoyear?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using 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.

For professional suppport, don't hesitate to chat with us.

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.

Spread the word¶

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on