Home / Statistical Tools / Data Types / DateTime Grouping
DateTime Grouping¶
When you have a column with DateTime data, you can group the values by different time periods for analysis. This is useful for summarizing data over time—for example, viewing monthly sales totals or daily defect counts.
Grouping Options¶
Quantum XL supports the following DateTime grouping options:
| Grouping | Description |
|---|---|
| None | No grouping; each unique date/time value is treated separately |
| Year | Group by year |
| Month | Group by year and month |
| Week | Group by the start date of the week |
| Day | Group by date (year, month, day) |
| Hour | Group by date and hour |
| Minute | Group by date, hour, and minute |
| Second | Group by date, hour, minute, and second |
Fully Qualified Grouping¶
Quantum XL uses fully qualified grouping, which means that all grouping levels include the higher-level time components. When you group by Month, you are actually grouping by Year and Month together. This ensures that data from different years remains in separate groups.
For example, if you group by Month and your data contains these dates:
| Row | Date |
|---|---|
| 1 | 1/1/2025 |
| 2 | 12/1/2025 |
| 3 | 1/1/2026 |
You will get three groups, not two:
| Group | Dates Included |
|---|---|
| January 2025 | Row 1 |
| December 2025 | Row 2 |
| January 2026 | Row 3 |
The two January dates (rows 1 and 3) are in different groups because they are in different years. The year is a "higher" level than month in the time hierarchy.
This principle applies to all grouping levels:
| Grouping | Includes These Components |
|---|---|
| Year | Year only |
| Month | Year + Month |
| Week | Year + Week (as start date) |
| Day | Year + Month + Day |
| Hour | Year + Month + Day + Hour |
| Minute | Year + Month + Day + Hour + Minute |
| Second | Year + Month + Day + Hour + Minute + Second |
Need Cyclical Grouping?
The DateTime grouping options above are fully qualified, meaning January 2025 and January 2026 are separate groups. If you need cyclical grouping—where all Januaries are combined regardless of year, or all Mondays regardless of week—see Cyclical Grouping for how to achieve this using Excel formulas with the Nominal data type.
Group Key Formats¶
When Quantum XL groups your data, it calculates a group key for each row based on the grouping option you selected. Rows that have the same group key are placed into the same group.
How Group Keys Work
The group key is an internal identifier that determines which rows belong together. For example, if two dates produce the group key "2025-01", they will be grouped together. If another date produces "2025-02", it will be in a separate group.
Each grouping option produces a specific key format:
| Grouping | Key Format | Example |
|---|---|---|
| None | Full date/time string | "1/15/2025 10:30:45 AM" |
| Year | Year number | "2025" |
| Month | Year-Month | "2025-01" |
| Week | Start date of week | "2025-01-12" |
| Day | Full date | "2025-01-15" |
| Hour | Date and hour | "2025-01-15 10" |
| Minute | Date, hour, minute | "2025-01-15 10:30" |
| Second | Date, hour, minute, second | "2025-01-15 10:30:45" |
Examples by Grouping Type¶
The following examples show how different dates are grouped under each option.
Year Grouping¶
| Original Date | Group Key |
|---|---|
| January 15, 2025 10:30 AM | 2025 |
| July 4, 2025 3:00 PM | 2025 |
| January 1, 2026 12:00 AM | 2026 |
Result: 2 groups (2025 and 2026)
Month Grouping¶
| Original Date | Group Key |
|---|---|
| January 15, 2025 | 2025-01 |
| January 31, 2025 | 2025-01 |
| February 1, 2025 | 2025-02 |
| January 15, 2026 | 2026-01 |
Result: 3 groups (2025-01, 2025-02, 2026-01)
Week Grouping¶
| Original Date | Group Key (Week Start) |
|---|---|
| Monday, January 13, 2025 | 2025-01-12 |
| Wednesday, January 15, 2025 | 2025-01-12 |
| Monday, January 20, 2025 | 2025-01-19 |
Result: 2 groups (weeks starting 2025-01-12 and 2025-01-19)
Day Grouping¶
| Original Date | Group Key |
|---|---|
| January 15, 2025 9:00 AM | 2025-01-15 |
| January 15, 2025 5:30 PM | 2025-01-15 |
| January 16, 2025 9:00 AM | 2025-01-16 |
Result: 2 groups (2025-01-15 and 2025-01-16)
Hour Grouping¶
| Original Date | Group Key |
|---|---|
| January 15, 2025 10:15 AM | 2025-01-15 10 |
| January 15, 2025 10:45 AM | 2025-01-15 10 |
| January 15, 2025 11:00 AM | 2025-01-15 11 |
Result: 2 groups (10:00 hour and 11:00 hour on 2025-01-15)
Minute Grouping¶
| Original Date | Group Key |
|---|---|
| January 15, 2025 10:30:15 AM | 2025-01-15 10:30 |
| January 15, 2025 10:30:45 AM | 2025-01-15 10:30 |
| January 15, 2025 10:31:00 AM | 2025-01-15 10:31 |
Result: 2 groups (10:30 and 10:31 on 2025-01-15)
Second Grouping¶
| Original Date | Group Key |
|---|---|
| January 15, 2025 10:30:45.100 | 2025-01-15 10:30:45 |
| January 15, 2025 10:30:45.999 | 2025-01-15 10:30:45 |
| January 15, 2025 10:30:46.000 | 2025-01-15 10:30:46 |
Result: 2 groups (10:30:45 and 10:30:46 on 2025-01-15)
Week Grouping Details¶
Week grouping uses the start date of the week as the group key, not a week number. For example, dates falling in the week of January 12-18, 2025 would have the group key "2025-01-12".
The first day of the week is determined by your system's regional settings (specifically, the CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek setting). Common configurations:
- United States: Sunday is typically the first day of the week
- Most of Europe: Monday is typically the first day of the week
Week Start Date
Because weeks are identified by their start date rather than a week number, the group key clearly indicates which specific week the data belongs to, avoiding ambiguity that can occur with week numbers at year boundaries.