# Date dimension

Build a table with one row per day and its fiscal year, quarter, period, week, bounds and labels, and load it into pandas or a SQL database.

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](https://fiscalclock.karthik.nz/reference/spec/api-v1/#dimension)).

## Into pandas or SQL

This example builds the dimension for fiscal year 2023 of the example definition from [the quick start](https://fiscalclock.karthik.nz/start-here/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.

**Python**

*dimension_frame.py*

```python
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])
```

*Output*

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

**JavaScript**

*dimension_frame.mjs*

```js
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);
}
```

*Output*

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

**Rust**

*dimension_frame.rs*

```rust
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(())
}
```

*Output*

```txt
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](https://fiscalclock.karthik.nz/reference/spec/api-v1/#dimension)):

| Columns | Meaning |
| - | - |
| `date` | The day, as `YYYY-MM-DD`. |
| `calendar_id` | The [identifier of the definition](https://fiscalclock.karthik.nz/guides/write-a-definition/#two-definitions-one-calendar) 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](https://fiscalclock.karthik.nz/guides/dates-and-intervals/#half-open-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:

**Python**

*dimension_csv.py*

```python
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="")
```

*Output*

```txt
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
```

**JavaScript**

*dimension_csv.mjs*

```js
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"));
```

*Output*

```txt
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
```

**Rust**

*dimension_csv.rs*

```rust
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(())
}
```

*Output*

```txt
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
```