> For the complete documentation index, see [llms.txt](https://docs.avonnicomponents.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/group-a-pivot-table-by-date.md).

# Group a Pivot Table by Date

Group a Pivot Table on a date field by year, quarter or month, so a finance lead reads closed won revenue per period instead of per close date.

A finance lead preparing the quarterly review wants closed won revenue per quarter, split between new and existing business, then the same revenue month by month. Grouping on **Close Date** with a **Date Grouping** turns hundreds of close dates into a few periods. **Display Full Label** prints the months as names instead of numbers.

<figure><img src="https://27923732-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1FUd4apB9YHgCEMUFbVb%2Fuploads%2FE9YxiNxpcxMONobbf8C6%2Fpivot-table-group-a-pivot-table-by-date-result.png?alt=media" alt="Pivot Table with closed won revenue grouped by year then quarter down the side, new and existing business across the top, and a grand total"><figcaption><p>Closed won revenue by year and quarter, split by type of business.</p></figcaption></figure>

## Before you start

* A screen flow with a Pivot Table on **Opportunity**, built as in [Show a Pipeline Summary and Drill Into Any Cell](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/show-a-pipeline-summary-and-drill-into-any-cell.md). This page only changes its query filter and its grouping.
* A date or date/time field to group on. Here, **Close Date**.

## Steps

{% stepper %}
{% step %}

### Keep only the closed won deals

Select the Pivot Table on the screen and click **Open Component Builder**. Under the query **Filters**, replace the **Closed** Equals `false` condition with **Stage** Equals `Closed Won`.

Keep the open pipeline filter instead if you want to group open deals by their expected close date. Everything below works the same way.
{% endstep %}

{% step %}

### Group the rows by year and quarter

In **Data Mappings**:

1. Under **Group Rows**, change the **Field** of the existing item from **Region** to **Close Date**.
2. A **Date Grouping** option appears as soon as the field is a date or date/time field. Set it to **Year-Quarter**.
3. Under **Group Columns**, change **Stage** to **Type**, so new and existing business sit side by side.
4. Under **Aggregations**, keep **Amount** with **Sum**.

**Year-Quarter** builds two levels: the year, then each quarter inside it. Their headers read **Close Date (Year)** and **Close Date (Quarter)**. To rename one, turn on **Custom Label** and type a **Label** such as `Quarter`.

<figure><img src="https://27923732-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1FUd4apB9YHgCEMUFbVb%2Fuploads%2FzV9rQiuBM4lMtjtftcdY%2Fpivot-table-group-a-pivot-table-by-date-date-grouping.png?alt=media" alt="Group Rows item in the property editor with Close Date as field and Year-Quarter as Date Grouping, and the Display Full Label toggle under it" width="320"><figcaption><p>Date Grouping appears once the field is a date.</p></figcaption></figure>
{% endstep %}

{% step %}

### Pick the right period

Each **Date Grouping** option returns numbers, not dates:

| Date Grouping    | Groups records by                     | Values shown             |
| ---------------- | ------------------------------------- | ------------------------ |
| **Year**         | Calendar year                         | `2025`, `2026`           |
| **Quarter**      | Calendar quarter, all years together  | `1` to `4`               |
| **Month**        | Month, all years together             | `1` to `12`              |
| **Week**         | Week of the year, all years together  | `1` to `53`              |
| **Day**          | Day of the month, all months together | `1` to `31`              |
| **Year-Quarter** | Year, then quarter                    | `2026`, then `1` to `4`  |
| **Year-Month**   | Year, then month                      | `2026`, then `1` to `12` |
| **Year-Week**    | Year, then week                       | `2026`, then `1` to `53` |

**Quarter**, **Month**, **Week** and **Day** ignore the year: the first quarter of 2025 and the first quarter of 2026 add up in the same row. When the records span more than one year, use the **Year-** version.
{% endstep %}

{% step %}

### Show months by name

To read the same revenue month by month:

1. Change the **Date Grouping** of **Close Date** to **Year-Month**.
2. Turn on **Display Full Label**, under **Date Grouping**.

The month rows now read `January` to `December` instead of `1` to `12`. **Display Full Label** only renames months: years, quarters, weeks and days stay numbers, and month names are always in English.

<figure><img src="https://27923732-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1FUd4apB9YHgCEMUFbVb%2Fuploads%2FqEjVwe0Dg9k4GJsXZ4Ic%2Fpivot-table-group-a-pivot-table-by-date-full-month.png?alt=media" alt="Pivot Table grouped by year then month, with month names such as January and February instead of numbers"><figcaption><p>Display Full Label prints months by name.</p></figcaption></figure>
{% endstep %}

{% step %}

### Save and test

Save the flow and click **Debug**. With **Year-Quarter**, each year is a row, with its quarters under it. The columns read **Existing Business** and **New Business**, and each cell holds the closed won amount for that quarter.

Switch to **Year-Month** with **Display Full Label**: each year now lists its months by name, and only months with closed won deals appear.
{% endstep %}
{% endstepper %}

{% hint style="info" %}
Two row levels also unlock a subtotal per year. [Show Totals, Subtotals and Stacked Summaries](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/show-totals-subtotals-and-stacked-summaries.md) covers it.
{% endhint %}

## Troubleshooting

<details>

<summary>The Date Grouping option does not appear</summary>

It only shows for a date or date/time field. A text field holding a date, such as a formula that returns text, groups by its exact value. Pick a real date field, such as **Close Date**.

</details>

<details>

<summary>The quarters do not match our fiscal year</summary>

**Quarter** and **Year-Quarter** use calendar quarters: quarter `1` is January to March. There is no fiscal year option on the Pivot Table.

</details>

<details>

<summary>Day gives 31 rows instead of one row per date</summary>

**Day** groups by day of the month across every month, so the 3rd of March and the 3rd of June share a row. To get one row per calendar date, leave **Date Grouping** empty on that field.

</details>

<details>

<summary>A record created late in the evening lands in the next day or month</summary>

On a date/time field, such as **Created Date**, the periods follow GMT, not the user's time zone. Group on a date field when one carries the same meaning.

</details>

## Related tutorials

* [Show a Pipeline Summary and Drill Into Any Cell](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/show-a-pipeline-summary-and-drill-into-any-cell.md): build the Pivot Table this page starts from.
* [Show Totals, Subtotals and Stacked Summaries](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/show-totals-subtotals-and-stacked-summaries.md): add a subtotal per year to the grouping above.
* [Let Users Filter a Pivot Table](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/let-users-filter-a-pivot-table.md): let the reader narrow the quarters to one lead source or stage.
* [Export a Pivot Table and Add Header Actions](https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/export-a-pivot-table-and-add-header-actions.md): send the quarterly figures to Excel.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.avonnicomponents.com/flow/tutorials/components/pivot-table/group-a-pivot-table-by-date.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
