Months from dates, and the two ways to filter

◈ 8 cards

A real date field groups into months inside the pivot — Jan 1610 · Feb 2560 · Mar 1970; a text date refuses. In R the bucket is format(as.Date(…), "%Y-%m"). A slicer filters every pivot connected to it; the Filters zone filters one; hiding source rows filters none.

Sales by month, without a month column

The question how did sales move month to month? needs a category the sheet does not have — the month — and a pivot can make it on the fly. Drop order_date into Rows and Sum of amount into Values: twelve rows, one per date, useless. Now group the date field into months. In Excel, right-click a date in the pivot → Group → Months (Excel 365 often groups a date field into months automatically the moment it lands in Rows); in Sheets, right-click → Create pivot date group → Month. The pivot collapses to:

order_date   Sum of amount
Jan                   1610
Feb                   2560
Mar                   1970
Grand Total           6140

January 420 + 180 + 760 + 250 = 1610; February 2560; March 1970. Quarters, years and days are the same command with a different unit.

When Group refuses

Grouping works only on a real date field — Module 2's serial number with a date format. Paste the dates in as text ("2025-01-06" left-aligned, or the imported 06/01/2025 that Lesson 5.3 warned about) and Excel answers Cannot group that selection, while Sheets offers no date group at all. The pivot is not broken and the data is not too small; the column is text. Convert it (DATEVALUE, or Text to Columns with a date type) and refresh, and Group works. Every "why won't my dates group?" question has this answer.

R builds the bucket as a derived column, then aggregates on it:

> orders$month <- format(as.Date(orders$order_date), "%Y-%m")
> aggregate(amount ~ month, data = orders, FUN = sum)
    month amount
1 2025-01   1610
2 2025-02   2560
3 2025-03   1970

as.Date makes a real date (the ISO text needs no format argument), and format(…, "%Y-%m") writes it back as a yyyy-mm label that sorts correctly as text — which is why the bucket is 2025-01, not Jan.

Three ways to filter, and what each one filters

The Filters zone — channel dropped there with Online chosen — restricts the rows this one pivot reads: Jan 670, Feb 1920, Mar 470. The source sheet is untouched.

A slicer is the Filters zone as a set of buttons — one per value of the field — that can be connected to several pivots at once. Add a slicer on channel, connect it to the by-month pivot and the by-province pivot (Excel: Report Connections; in Sheets a slicer applies to every pivot and chart on its sheet that shares the data range), and one click on Store filters both. That is the mechanism of a dashboard (Module 14): one control, many views. Sheets has had slicers since 2019.

Filtering the source sheet with the funnel does nothing to a pivot: a pivot reads every row of its range, hidden or not. Hiding the Store rows on the sheet and refreshing still gives 6140. To exclude rows from a pivot, use the Filters zone or a slicer — or delete the rows, which you do not want.

Type the R pair, then read February

Type the month bucket and the aggregate. The numeric item asks for February's total.

CRISP-DM: deriving the month is data preparation → construct data; the by-month pivot is data understanding → explore data.