--- title: "Calendars" framework: angular version: "2.1.2" --- # Calendars Studio groups and analyses date fields through calendars - named time dimensions declared in the `data` definition alongside your sources. A calendar owns a set of fragments (Year, Month, Day of Week, …) that appear in the Data Panel and can be used like normal fields. ## Declaring a Calendar Add a `calendars` array to the `data` definition, alongside `sources` and `relationships`. Each calendar declares its `id`, display `label`, the date `range` it covers, and the fragments to expose. Range endpoints are literal date strings, epoch-ms numbers, or field references (with `aggregation: 'min' | 'max'` to detect the range from a column's data): ```typescript const studioProperties = { data: { sources: [{ id: 'sales', data: [...], fields: [...] }], calendars: [ { id: 'calendar', label: 'Calendar', range: { from: { type: 'date', value: '2022-01-01' }, to: { type: 'date', value: '2024-12-31' } }, fragments: [ 'year', 'quarter', 'month', 'week', 'day', 'monthOfYear', 'dayOfWeek' ], }, ], }, }; ``` ## Binding Date Columns Bindings are declared as `relationships` in the data sources definition. To bind a fact date column to a calendar, add a relationship whose `target.calendarId` matches the calendar's `id`: ```typescript const studioProperties = { data: { sources: [...], relationships: [ { id: 'sales-calendar', source: { tableId: 'sales', fieldId: 'order_date' }, target: { calendarId: 'calendar' }, }, ], calendars: [ { id: 'calendar', label: 'Calendar', range: { from: { type: 'date', value: '2022-01-01' }, to: { type: 'date', value: '2024-12-31' } }, fragments: ['year', 'quarter', 'month'], }, ], }, }; ``` When a table has more than one date column, add a separate relationship entry for each column - each relationship binds one column independently. The grain a binding can reach comes from the bound column's data type. A `date` column reaches `day`; a datetime column also reaches `hour` and `minute`. A column's grain menu only offers fragments its type supports; referencing a finer fragment directly reports a validation error rather than returning wrong results. ### Datetime Columns When binding a datetime column you can `truncate` it to a coarser grain before it joins the calendar - useful when the time component is noise and you only want to group by day: ```typescript const relationships = [ { id: 'events-calendar', source: { tableId: 'events', fieldId: 'occurred_at' }, // a datetime column target: { calendarId: 'calendar' }, truncate: 'day', // drop the time component; group at day grain }, ]; ``` To group by time of day, declare the sub-day fragments (`hour`, `minute`, `hourOfDay`) on the calendar alongside the date fragments: ```typescript const calendar = { id: 'calendar', label: 'Calendar', range: { from: { type: 'date', value: '2024-01-01' }, to: { type: 'date', value: '2024-01-31' } }, fragments: ['day', 'hour', 'minute', 'hourOfDay'], }; ``` Sub-day grains generate far more spine rows than date grains (minute grain over a single day is 1,440 rows; over a year, more than half a million). Keep the calendar `range` tight when exposing `hour` or `minute`. ## Fragment Types Fragments fall into two categories: - **Chronological** fragments - each value is a distinct point in time. E.g. `month` produces one row per calendar month (January 2023 and January 2024 are separate rows). - **Cyclic** fragments - values are bucketed together. E.g. all Januaries from any year map to the same bucket. | Category | Fragments | | --- | --- | | Chronological | `year`, `halfYear`, `quarter`, `month`, `week`, `day`, `hour`, `minute` | | Cyclic | `monthOfYear`, `dayOfWeek`, `monthOfQuarter`, `weekOfMonth`, `halfOfYear`, `hourOfDay` | The `hour` and `minute` chronological fragments and the `hourOfDay` cyclic fragment are sub-day grains. They only produce values when the bound column carries a time component - see [Datetime Columns](#datetime-columns). ## Using Calendar Fragments in Widgets Reference calendar fragments in `dataMapping` using the format `calendarId::fragmentId`: ```typescript const studioProperties = { initialState: { pages: [{ widgets: { 'revenue-by-month': { type: 'line-chart', dataMapping: { categoryKey: [{ id: 'calendar::month' }], valueKey: [{ id: 'sales.amount', aggregation: 'sum' }], tooltipKey: [], }, }, }, }], }, }; ``` The `calendar` prefix is the calendar's `id`. Fragment fields are visible in the data panel under the calendar's name alongside the regular data source tables. #### Time Data ```ts import '@angular/compiler'; import { provideHttpClient } from '@angular/common/http'; import { bootstrapApplication } from '@angular/platform-browser'; import { AppComponent } from './app.component.ts'; const app = bootstrapApplication(AppComponent, { providers: [provideHttpClient()], }); ``` [Live example: Time Data](https://www.ag-grid.com/studio/examples/calendars/time-data-example/angular/) The example above shows three pages: - **By Year** - `calendar::year` (chronological; one bar per calendar year) - **By Month** - `calendar::month` (chronological; one line point per calendar month) - **Seasonality** - `calendar::monthOfYear` and `calendar::dayOfWeek` (cyclic; all years folded together) ## Fragment Reference Each fragment has a default display format. The table below lists every built-in fragment. | Fragment | Category | Default format | Notes | | --- | --- | --- | --- | | `year` | chronological | `yyyy` | | | `halfYear` | chronological | `H1 yyyy` | H1 = Jan - Jun, H2 = Jul - Dec | | `quarter` | chronological | `"Q"qq yyyy` | | | `month` | chronological | `mmm yyyy` | | | `week` | chronological | `"W"ww yyyy` | ISO week (Mon start) | | `day` | chronological | `dd/mm/yyyy` (or locale equivalent) | | | `hour` | chronological | `dd/mm/yyyy hh:00` (or locale equivalent) | Datetime columns only | | `minute` | chronological | `dd/mm/yyyy hh:mm` (or locale equivalent) | Datetime columns only | | `monthOfYear` | cyclic | `mmmm` | Renders as month name (January - December) | | `dayOfWeek` | cyclic | `ddd` | ISO (1 = Mon) | | `monthOfQuarter` | cyclic | `0` | 1 - 3 | | `weekOfMonth` | cyclic | `0` | 1 - 5 | | `halfOfYear` | cyclic | `H1` | H1 or H2, all years folded together | | `hourOfDay` | cyclic | `0` | 0 - 23, datetime columns only | To override the format for a specific fragment, replace the bare string in `fragments` with an object: ```typescript const calendar = { id: 'calendar', label: 'Calendar', range: { from: { type: 'date', value: '2022-01-01' }, to: { type: 'date', value: '2024-12-31' } }, fragments: [ 'year', { unit: 'month', format: 'mmmm yyyy' }, // override: spell out month name 'monthOfYear', ], } ``` The `format` string uses the same Excel-like pattern as field `format` definitions elsewhere in Studio (`d`, `mmm`, `yyyy`, `"Q"q`, etc.). The example below demonstrates all fragments. #### All Fragments ```ts import '@angular/compiler'; import { provideHttpClient } from '@angular/common/http'; import { bootstrapApplication } from '@angular/platform-browser'; import { AppComponent } from './app.component.ts'; const app = bootstrapApplication(AppComponent, { providers: [provideHttpClient()], }); ``` [Live example: All Fragments](https://www.ag-grid.com/studio/examples/calendars/all-fragments/angular/) ## Custom Bucket Types The built-in fragments cover the common calendar vocabulary, but some groupings are specific to a business - a biweekly pay period, a retail 4-4-5 period, a fiscal week. Register additional bucket types with `createBuckets`, then reference their `id`s in a calendar's `fragments` array like any built-in fragment. ```typescript import { createBuckets } from 'ag-studio'; const PAY_PERIOD_EPOCH_DAY = Math.floor(Date.UTC(2022, 0, 3) / 86_400_000); const buckets = createBuckets({ additionalTypes: [ { id: 'payPeriod', grain: 'day', components: ['year', 'month', 'day'], encode: ([year, month, day]) => { const dayIndex = Math.floor(Date.UTC(year, month - 1, day) / 86_400_000); return Math.floor((dayIndex - PAY_PERIOD_EPOCH_DAY) / 14); }, decode: (payPeriod) => { const date = new Date((PAY_PERIOD_EPOCH_DAY + payPeriod * 14) * 86_400_000); return [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()]; }, format: () => (payPeriod) => `Pay Period ${payPeriod}`, }, ], }); ``` Pass the result as `data.buckets`, alongside `sources` and `calendars`: ```typescript const data = { sources: [ /* ... */ ], calendars: [ { id: 'payroll', label: 'Payroll Calendar', range: { from: { type: 'date', value: '2020-01-01' }, to: { type: 'date', value: '2030-12-31' } }, fragments: ['year', 'payPeriod'], }, ], buckets, }; ``` A bucket with more than one `components` entry needs `encode` and `decode` to derive a single, sequential integer key from those components - `createBuckets` throws if either is missing. A single-component bucket (e.g. `components: ['month']`) needs neither; the raw extracted value is the key. `createBuckets` also accepts `overrides` to change a built-in bucket's `format` without redeclaring its `grain`, `components`, `encode`, or `decode`: ```typescript const buckets = createBuckets({ overrides: [{ id: 'month', format: 'mmmm yyyy' }], }); ``` Custom buckets are available on any date or datetime column, whether or not it is bound to a calendar. Drop the column onto a widget and the custom fragment appears in its grain menu alongside the built-in units - on a calendar-bound column it sits beside the calendar's declared fragments, so binding a column never hides its buckets. #### Custom Bucket Types ```ts import '@angular/compiler'; import { provideHttpClient } from '@angular/common/http'; import { bootstrapApplication } from '@angular/platform-browser'; import { AppComponent } from './app.component.ts'; const app = bootstrapApplication(AppComponent, { providers: [provideHttpClient()], }); ``` [Live example: Custom Bucket Types](https://www.ag-grid.com/studio/examples/calendars/custom-bucket-example/angular/) The example above registers `payPeriod` (14-day periods counted from a fixed reference date, with no alignment to calendar months or years) alongside the built-in `year` fragment on the same calendar. Both fragments are addressed the same way in `dataMapping`: `payroll::year` and `payroll::payPeriod`. ## Adopted Calendars If you already maintain a date table, it can be used as-is without needing to generate a new one. Declare an adopted calendar by setting `range.sourceId` to the source ID of the date table. Add a `mappings` array that declares which field in that table provides which calendar fragment: ```typescript const calendar = { id: 'calendar', label: 'Calendar', range: { sourceId: 'dimDate', mappings: [ { fieldId: 'year_month', components: ['year', 'month'] }, // e.g. yyyymm integer { fieldId: 'month_of_year', components: ['monthOfYear'] }, ], }, } ``` Studio computes each fragment from the bound fact date column using the same formula as the mapping, so the two sides always agree without an explicit join key. Sub-year fragment values must be unique within a year - a bare month number of `4` is ambiguous across years. Pair the fragment with `year` in the same `components` array. The accessor receives the full source row, so it can read any column - pair from the same packed field, or from two separate columns: ```typescript const mappings = [ // packed: year_month stores yyyymm, e.g. 202304 { fieldId: 'year_month', components: ['year', 'month'], accessor: (row) => [Math.floor(row.year_month / 100), row.year_month % 100], }, // separate columns: year_num and month_num on the same row { fieldId: 'month_num', components: ['year', 'month'], accessor: (row) => [row.year_num, row.month_num], }, ]; ``` The `components` order and the accessor's return order match positionally. Two cases to watch: - **`day`** must include `year` and `month` - e.g. a `yyyymmdd` field unpacked as `[year, month, day]`. - **`week`** must pair with the ISO week-numbering year, not the calendar year - they diverge in the days around 1 January. ### Packed Columns and Accessors When a single field encodes several components (such as a packed `yyyymmdd` integer), add an `accessor` function to the mapping. It receives the full data row and returns the component values in the order declared by `components`: ```typescript const calendar = { id: 'calendar', label: 'Calendar', range: { sourceId: 'dimDate', mappings: [ { fieldId: 'yyyymmdd', components: ['year', 'month'], accessor: (row) => [ Math.floor(row.yyyymmdd / 10000), Math.floor((row.yyyymmdd % 10000) / 100), ], }, { fieldId: 'month_of_year', components: ['monthOfYear'] }, ], }, } ``` ### Binding to an Adopted Calendar Bind fact date columns to an adopted calendar the same way as a generated calendar - add a relationship with `target.calendarId` set to the adopted calendar's `id`: ```typescript const relationships = [ { id: 'sales-dimdate', source: { tableId: 'sales', fieldId: 'order_date' }, target: { calendarId: 'calendar' }, }, ], ``` The calendar type is inferred from its shape - a calendar whose `range` has a `sourceId` property is adopted; one without is generated. #### Adopted Calendar ```ts import '@angular/compiler'; import { provideHttpClient } from '@angular/common/http'; import { bootstrapApplication } from '@angular/platform-browser'; import { AppComponent } from './app.component.ts'; const app = bootstrapApplication(AppComponent, { providers: [provideHttpClient()], }); ``` [Live example: Adopted Calendar](https://www.ag-grid.com/studio/examples/calendars/adopted-calendar-example/angular/) The example above uses a pre-built date table with a packed `yyyymmdd` integer and a `month_of_year` column. The calendar unpacks `year` and `month` from `yyyymmdd` via an `accessor`, and reads `month_of_year` directly. Widgets reference those fragments with the same `calendarId::fragmentId` syntax as generated calendars. The fourth page shows the raw date table columns for reference. The same pattern works for string-encoded dates. If the date table stores months as `"YYYY-MM"` strings, split and parse in the accessor: ```typescript { fieldId: 'year_month', components: ['year', 'month'], accessor: (row) => { const [y, m] = (row.year_month as string).split('-'); return [parseInt(y, 10), parseInt(m, 10)]; }, } ``` #### String Calendar ```ts import '@angular/compiler'; import { provideHttpClient } from '@angular/common/http'; import { bootstrapApplication } from '@angular/platform-browser'; import { AppComponent } from './app.component.ts'; const app = bootstrapApplication(AppComponent, { providers: [provideHttpClient()], }); ``` [Live example: String Calendar](https://www.ag-grid.com/studio/examples/calendars/string-calendar-example/angular/) The example above uses a date table where months are stored as `"YYYY-MM"` strings. The accessor splits the string and returns `[year, month]` as integers. The `month_of_year` column is read directly with no accessor needed.