Home / Statistical Tools / Data Types / Cyclical Grouping
Cyclical Grouping¶
Cyclical grouping (also called relative or periodic grouping) combines all instances of the same time period regardless of when they occurred. For example, grouping "all Januaries together" across multiple years, or "all Mondays together" across multiple weeks.
Quantum XL's built-in DateTime grouping is fully qualified, meaning it always includes higher-level time components. To achieve cyclical grouping, you can use Excel formulas to extract the time component you want, then use that column with the Nominal data type in Quantum XL.
How It Works¶
- Create a new column in Excel with a formula that extracts the time period you want
- In Quantum XL, set that column's data type to Nominal
- Use that column for grouping in your analysis
The formula converts the date/time into a text label (like "Monday" or "January"), and Quantum XL groups all rows with the same label together.
Formulas by Time Period¶
The following table shows Excel formulas for each type of cyclical grouping. In these examples, assume your date/time data is in column A.
| Cyclical Grouping | Excel Formula | Example Output |
|---|---|---|
| Month of Year | =TEXT(A1,"MMMM") |
"January", "February", ... |
| Day of Week | =TEXT(A1,"dddd") |
"Monday", "Tuesday", ... |
| Day of Month | =DAY(A1) |
1, 2, 3, ... 31 |
| Hour of Day | =HOUR(A1) |
0, 1, 2, ... 23 |
| Minute of Hour | =MINUTE(A1) |
0, 1, 2, ... 59 |
| Second of Minute | =SECOND(A1) |
0, 1, 2, ... 59 |
| Week of Year | =WEEKNUM(A1) |
1, 2, 3, ... 52 |
| Quarter | ="Q" & CEILING(MONTH(A1)/3,1) |
"Q1", "Q2", "Q3", "Q4" |
Examples¶
Example 1: Group by Day of Week¶
You want to analyze sales patterns by day of the week, combining all Mondays together, all Tuesdays together, etc.
Step 1: Add a formula column in Excel:
| Row | Date (Column A) | Day of Week (Column B) |
|---|---|---|
| 1 | 1/6/2025 | =TEXT(A1,"dddd") → "Monday" |
| 2 | 1/7/2025 | =TEXT(A2,"dddd") → "Tuesday" |
| 3 | 1/13/2025 | =TEXT(A3,"dddd") → "Monday" |
| 4 | 1/14/2025 | =TEXT(A4,"dddd") → "Tuesday" |
Step 2: In Quantum XL, set column B to Nominal data type.
Step 3: Use column B for grouping. Rows 1 and 3 will be grouped together (Monday), and rows 2 and 4 will be grouped together (Tuesday).
Example 2: Group by Month of Year¶
You want to compare all Januaries across multiple years to see seasonal patterns.
Step 1: Add a formula column in Excel:
| Row | Date (Column A) | Month (Column B) |
|---|---|---|
| 1 | 1/15/2024 | =TEXT(A1,"MMMM") → "January" |
| 2 | 6/20/2024 | =TEXT(A2,"MMMM") → "June" |
| 3 | 1/10/2025 | =TEXT(A3,"MMMM") → "January" |
| 4 | 6/15/2025 | =TEXT(A4,"MMMM") → "June" |
Step 2: In Quantum XL, set column B to Nominal data type.
Step 3: Use column B for grouping. Rows 1 and 3 will be grouped together (January from both years), and rows 2 and 4 will be grouped together (June from both years).
Example 3: Group by Hour of Day¶
You want to analyze defects by hour to identify patterns in shift performance.
Step 1: Add a formula column in Excel:
| Row | Timestamp (Column A) | Hour (Column B) |
|---|---|---|
| 1 | 1/15/2025 8:15 AM | =HOUR(A1) → 8 |
| 2 | 1/15/2025 2:30 PM | =HOUR(A2) → 14 |
| 3 | 1/16/2025 8:45 AM | =HOUR(A3) → 8 |
| 4 | 1/16/2025 2:10 PM | =HOUR(A4) → 14 |
Step 2: In Quantum XL, set column B to Nominal data type.
Step 3: Use column B for grouping. Rows 1 and 3 will be grouped together (8 AM hour from both days), and rows 2 and 4 will be grouped together (2 PM hour from both days).
Alternative Formula Formats¶
Depending on your needs, you may want different output formats:
Month Variations¶
| Formula | Output |
|---|---|
=TEXT(A1,"MMMM") |
"January" (full name) |
=TEXT(A1,"MMM") |
"Jan" (abbreviated) |
=MONTH(A1) |
1 (number) |
=TEXT(A1,"MM") |
"01" (two-digit) |
Day of Week Variations¶
| Formula | Output |
|---|---|
=TEXT(A1,"dddd") |
"Monday" (full name) |
=TEXT(A1,"ddd") |
"Mon" (abbreviated) |
=WEEKDAY(A1) |
2 (number, Sunday=1) |
=WEEKDAY(A1,2) |
1 (number, Monday=1) |
Hour Variations¶
| Formula | Output |
|---|---|
=HOUR(A1) |
14 (24-hour format) |
=TEXT(A1,"h AM/PM") |
"2 PM" (12-hour with AM/PM) |
=TEXT(A1,"hh") |
"14" (two-digit 24-hour) |
Tips¶
Sorting Cyclical Groups
When using text labels like "January" or "Monday", the groups may not sort in the order you expect (they will sort alphabetically). If sort order matters, consider using numeric formulas like =MONTH(A1) instead of =TEXT(A1,"MMMM").
Combining Time Periods
You can combine multiple time components for more specific cyclical analysis. For example, to group by day of week AND hour:
=TEXT(A1,"dddd") & " " & HOUR(A1) & ":00"