Calendar

In the XpertAI Data Analysis Platform, the Calendar Dimension (also referred to as the "Time Dimension") serves as the foundation for various time-series analyses, enabling users to flexibly slice data, perform trend analysis, and conduct year-over-year or period-over-period comparisons based on time granularities such as year, quarter, month, week, and day.

✨ Feature Overview

The Calendar Dimension provides a standardized hierarchical time structure, allowing users to switch between different time granularities to view data performance. For example:

• Year: Suitable for annual comparisons, yearly summary analysis, etc.
• Quarter: Supports observing sales or operational trends by quarter.
• Month: Commonly used for monthly reports and month-over-month analysis.
• Week: Ideal for cyclical weekly operational analysis.
• Day: Enables viewing data for specific dates, such as daily sales or metric fluctuations.

🧭 Time Hierarchy and Semantic Explanation

The platform includes a built-in standard time hierarchy system, with each level having clear semantics and formatting rules:

Level Name	Description	Format String	Example Value
Calendar Year	Year granularity	yyyy	2024
Calendar Quarter	Quarter granularity	[yyyy].[Q#]	[2024].[Q1]
Calendar Month	Month granularity	[yyyy].[MM]	[2024].[01]
Calendar Week	Week granularity	[yyyy].[wWW]	[2024].[w05]
Calendar Day	Day granularity	[yyyy].[MM].[dd]	[2024].[01].[15]

📌 Note: The format string is used to convert date objects into a unified time identifier (technical ID) in the analysis model to ensure accurate data matching and support for time functions.

For more details on configuring time dimension semantics, refer to: Semantics Settings

🎯 Usage Recommendations

• For annual, quarterly, or monthly analysis, use the corresponding time hierarchy for aggregation and leverage the platform’s time comparison features (e.g., current period vs. same period last year, year-over-year/month-over-month).
• In dashboards or data exploration, the time dimension is typically used as rows or columns to build trend charts, tables, or other visualization components.
• For cross-period analysis (e.g., year-to-date, month-to-date), the platform’s built-in intelligent time functions can be used directly without additional modeling.
• For using time filters, refer to Slicer #Time Dimension.

📎 Appendix: Creating a Time/Calendar Table

To support time dimension analysis, it is recommended to maintain a standard Time Calendar Table in the database, containing common fields such as year, quarter, month, week, and day. This table can serve as the primary table for the calendar dimension, referenced in the data analysis platform’s modeling.

✅ Table Structure Design

Recommended fields include:

Field Name	Type	Description
date_key	VARCHAR(10)	Primary key, format: YYYY-MM-DD
date	DATE	Date-type field
year	INT	Year
quarter	INT	Quarter (1–4)
month	INT	Month (1–12)
month_name	VARCHAR(20)	Month name (e.g., January)
week_in_year	INT	Week number in the year
day_in_month	INT	Day number in the month
day_name	VARCHAR(20)	Day of the week (e.g., Monday)

🧩 Example SQL

MySQL

-- Create table
CREATE TABLE time_calendar (
  date_key VARCHAR(10) PRIMARY KEY, -- Format: 'YYYY-MM-DD'
  date DATE,
  year INT,
  quarter INT,
  month INT,
  month_name VARCHAR(20),
  week_in_year INT,
  day_in_month INT,
  day_name VARCHAR(20)
);

-- Populate data (2023-01-01 to 2025-12-31)
WITH RECURSIVE dates AS (
  SELECT DATE('2023-01-01') AS dt
  UNION ALL
  SELECT dt + INTERVAL 1 DAY FROM dates WHERE dt < '2025-12-31'
)
INSERT INTO time_calendar
SELECT
  DATE_FORMAT(dt, '%Y-%m-%d') AS date_key,
  dt,
  YEAR(dt),
  QUARTER(dt),
  MONTH(dt),
  MONTHNAME(dt),
  WEEK(dt, 1),
  DAY(dt),
  DAYNAME(dt)
FROM dates;

PostgreSQL

-- Create table
CREATE TABLE time_calendar (
  date_key VARCHAR(10) PRIMARY KEY, -- Format: 'YYYY-MM-DD'
  date DATE,
  year INT,
  quarter INT,
  month INT,
  month_name VARCHAR(20),
  week_in_year INT,
  day_in_month INT,
  day_name VARCHAR(20)
);

-- Populate data (2023-01-01 to 2025-12-31)
INSERT INTO time_calendar
SELECT
  TO_CHAR(d, 'YYYY-MM-DD') AS date_key,
  d::DATE AS date,
  EXTRACT(YEAR FROM d)::INT AS year,
  EXTRACT(QUARTER FROM d)::INT AS quarter,
  EXTRACT(MONTH FROM d)::INT AS month,
  TO_CHAR(d, 'Month') AS month_name,
  EXTRACT(WEEK FROM d)::INT AS week_in_year,
  EXTRACT(DAY FROM d)::INT AS day_in_month,
  TO_CHAR(d, 'Day') AS day_name
FROM generate_series(
  DATE '2023-01-01',
  DATE '2025-12-31',
  INTERVAL '1 day'
) AS d;

📌 Notes

• date_key is the primary key, used as a unique identifier for each day in the semantic model.
• Retain the date type field for potential time function calculations or date range filtering.
• week_in_year should use ISO weeks (Monday as the start of the week) for consistency across databases.
• month_name and day_name string fields can be used as display fields.
• Additional time-related columns can be added as needed, such as whether it’s a workday, a holiday, or a custom fiscal period.
• TO_CHAR(d, 'YYYY-MM-DD') and DATE_FORMAT(dt, '%Y-%m-%d') ensure a standardized primary key format.