Date dimension

View as Markdown

A date dimension is a table with one row per calendar day and columns that describe the day, such as its fiscal year and quarter. Reports join it to other tables on the date, so that every report groups days into fiscal periods the same way. fiscalclock builds one from a definition with dimension, which returns rows, or dimension_csv, which returns the same rows as CSV text.

Both take a half-open range of dates, start up to but not including end_exclusive, of at most 146,097 days (400 years). An empty range gives no rows (API spec).

Into pandas or SQL

This example builds the dimension for fiscal year 2023 of the example definition from the quick start, using the year’s own bounds as the range, and counts the days in each quarter. The Python version loads the rows into a pandas DataFrame, writes them to an SQLite table and queries the first day of the extra week.

import sqlite3
from pathlib import Path

import pandas as pd
from fiscalclock import FiscalCalendar

cal = FiscalCalendar(Path("retail-4-5-4.json").read_text(encoding="utf-8"))

fy2023 = cal.period("year", 2023)
days = pd.DataFrame(cal.dimension(fy2023.start, fy2023.end_exclusive))
print(len(days))
print(days.groupby("label_nrf_quarter").size().to_string())

with sqlite3.connect(":memory:") as db:
    days.to_sql("fiscal_day", db, index=False)
    query = "SELECT date FROM fiscal_day WHERE week_is_extra_week ORDER BY date LIMIT 1"
    print(db.execute(query).fetchone()[0])

371
label_nrf_quarter
FY2023 Q1    91
FY2023 Q2    91
FY2023 Q3    91
FY2023 Q4    98
2024-01-28

import { readFileSync } from "node:fs";
import { FiscalCalendar } from "fiscalclock";

const cal = new FiscalCalendar(readFileSync("retail-4-5-4.json", "utf8"));

const fy2023 = cal.period("year", 2023);
const days = cal.dimension(fy2023.start, fy2023.end_exclusive);
console.log(days.length);
for (const [label, rows] of Object.entries(Object.groupBy(days, (day) => day.label_nrf_quarter))) {
  console.log(label, rows.length);
}

371
FY2023 Q1 91
FY2023 Q2 91
FY2023 Q3 91
FY2023 Q4 98

use std::error::Error;
use std::fs;

use fiscalclock::{FiscalCalendar, Level};

fn main() -> Result<(), Box<dyn Error>> {
    let cal = FiscalCalendar::from_json(&fs::read_to_string("retail-4-5-4.json")?)?;

    let fy2023 = cal.period(Level::Year, 2023, None)?;
    let days = cal.dimension(fy2023.start, fy2023.end_exclusive)?;
    println!("{}", days.len());
    for quarter in cal.children(&fy2023, Level::Quarter)? {
        let count = days.iter().filter(|day| day.location.quarter == quarter).count();
        println!("{} {}", cal.format(&quarter, None)?, count);
    }

    Ok(())
}

371
FY2023 Q1 91
FY2023 Q2 91
FY2023 Q3 91
FY2023 Q4 98

To load the dimension into another database, write the output of dimension_csv to a file and use the database’s CSV import.

Columns

Each row has these columns, in this order (API spec):

Columns	Meaning
`date`	The day, as `YYYY-MM-DD`.
`calendar_id`	The identifier of the definition that produced the row.
`fiscal_year`, `quarter`, `period`, `week`	The fiscal year number under the default scheme, and the 1-based index of the quarter, period and week within the year.
`day_of_year`, `day_of_quarter`, `day_of_period`, `day_of_week`	1-based positions of the day. `day_of_week` is 1 on `week.starts_on`.
`year_start`, `year_last_day`, and the same pair for `quarter`, `period` and `week`	The first and last day of each period containing the day.
`year_has_extra_week`, `period_has_extra_week`, `week_is_extra_week`	Whether the year or period contains the extra week, and whether the day’s week is the extra week.
`label_<scheme>_<level>`	One column per template in the definition, ordered by scheme name and then by level. A label that cannot be formatted is `null` in JSON and an empty field in CSV.

The bounds columns hold each period’s first and last day, both inclusive. The other operations also return each period’s end_exclusive, the day after its last day (Dates and intervals).

CSV

dimension_csv returns RFC 4180 CSV with a header row, \n line endings and a final newline. Booleans are true and false, and a field is quoted only when it contains a comma, a quote or a line break. All three packages return the same text:

from pathlib import Path

from fiscalclock import FiscalCalendar

cal = FiscalCalendar(Path("retail-4-5-4.json").read_text(encoding="utf-8"))

print(cal.dimension_csv("2024-02-03", "2024-02-05"), end="")

date,calendar_id,fiscal_year,quarter,period,week,day_of_year,day_of_quarter,day_of_period,day_of_week,year_start,year_last_day,quarter_start,quarter_last_day,period_start,period_last_day,week_start,week_last_day,year_has_extra_week,period_has_extra_week,week_is_extra_week,label_nrf_year,label_nrf_quarter,label_nrf_period,label_nrf_week
2024-02-03,efc8868a4b19eaab,2023,4,12,53,371,98,35,7,2023-01-29,2024-02-03,2023-10-29,2024-02-03,2023-12-31,2024-02-03,2024-01-28,2024-02-03,true,true,true,FY2023,FY2023 Q4,FY2023 P12,FY2023 W53
2024-02-04,efc8868a4b19eaab,2024,1,1,1,1,1,1,1,2024-02-04,2025-02-01,2024-02-04,2024-05-04,2024-02-04,2024-03-02,2024-02-04,2024-02-10,false,false,false,FY2024,FY2024 Q1,FY2024 P01,FY2024 W01

import { readFileSync } from "node:fs";
import { FiscalCalendar } from "fiscalclock";

const cal = new FiscalCalendar(readFileSync("retail-4-5-4.json", "utf8"));

process.stdout.write(cal.dimensionCsv("2024-02-03", "2024-02-05"));

date,calendar_id,fiscal_year,quarter,period,week,day_of_year,day_of_quarter,day_of_period,day_of_week,year_start,year_last_day,quarter_start,quarter_last_day,period_start,period_last_day,week_start,week_last_day,year_has_extra_week,period_has_extra_week,week_is_extra_week,label_nrf_year,label_nrf_quarter,label_nrf_period,label_nrf_week
2024-02-03,efc8868a4b19eaab,2023,4,12,53,371,98,35,7,2023-01-29,2024-02-03,2023-10-29,2024-02-03,2023-12-31,2024-02-03,2024-01-28,2024-02-03,true,true,true,FY2023,FY2023 Q4,FY2023 P12,FY2023 W53
2024-02-04,efc8868a4b19eaab,2024,1,1,1,1,1,1,1,2024-02-04,2025-02-01,2024-02-04,2024-05-04,2024-02-04,2024-03-02,2024-02-04,2024-02-10,false,false,false,FY2024,FY2024 Q1,FY2024 P01,FY2024 W01

use std::error::Error;
use std::fs;

use fiscalclock::{FiscalCalendar};

fn main() -> Result<(), Box<dyn Error>> {
    let cal = FiscalCalendar::from_json(&fs::read_to_string("retail-4-5-4.json")?)?;

    print!("{}", cal.dimension_csv("2024-02-03".parse()?, "2024-02-05".parse()?)?);

    Ok(())
}

date,calendar_id,fiscal_year,quarter,period,week,day_of_year,day_of_quarter,day_of_period,day_of_week,year_start,year_last_day,quarter_start,quarter_last_day,period_start,period_last_day,week_start,week_last_day,year_has_extra_week,period_has_extra_week,week_is_extra_week,label_nrf_year,label_nrf_quarter,label_nrf_period,label_nrf_week
2024-02-03,efc8868a4b19eaab,2023,4,12,53,371,98,35,7,2023-01-29,2024-02-03,2023-10-29,2024-02-03,2023-12-31,2024-02-03,2024-01-28,2024-02-03,true,true,true,FY2023,FY2023 Q4,FY2023 P12,FY2023 W53
2024-02-04,efc8868a4b19eaab,2024,1,1,1,1,1,1,1,2024-02-04,2025-02-01,2024-02-04,2024-05-04,2024-02-04,2024-03-02,2024-02-04,2024-02-10,false,false,false,FY2024,FY2024 Q1,FY2024 P01,FY2024 W01