Group dates by month, year or weekday in a pivot
In Group & Pivot, a date column in Rows or Columns is grouped by day unless you change its Edit format. Pick a coarser format to total by month, year or weekday. For building the pivot itself, see Group, pivot and unpivot data.
Choose how dates are grouped
When a date column is in Rows or Columns, its edit icon opens an Edit format list that sets how dates are grouped. The default is by day (DD-Mon-YYYY).
| Edit format | Groups by | Result column type |
|---|---|---|
| DD Mon, YYYY HH:MM:SS | Second | Date |
| DD Mon, YYYY HH:MM | Minute | Date |
| DD Mon, YYYY HH | Hour | Date |
| DD-Mon-YYYY (default) | Day | Date |
| YYYY, Month | Year and month | Text |
| YYYY (number icon) | Year | Number |
| Month | Month name, across all years | Text |
| YYYY (text icon) | Year | Text |
| Weekday | Day of the week, across all weeks | Text |
| Mon-DD, YYYY HH:MM:SS | Second, written as text | Text |
A format that isn't a date changes the column's type, and the menu says so before you save, for example: "Editing format of column "order_date", changes its type from "Date" to "Text"". A date column in Rows also has a Display Name box.
Example: to total sales by month, put order_date in Rows, set its Edit format to YYYY, Month, and put sales in Values with Sum.
Related
- Group, pivot and unpivot data: Rows, Columns and Values.
- Work with dates and financial years: extract a month or financial year to group by.
- How the pipeline works: the grouping re-runs on every refresh.
Something wrong? support@mammoth.io