Pivot Table Grouped by Financial Year

[flywire]

Looking at LO Calc | pivot table | ability to group daily values by month, year.... Can a pivot table group by Financial Year?

For me, the Financial year starts in the previous year on 1 July and ends in the current year on 30 June. Data > Group and Outline > Grouping does not support financial years. The gui is a bit glitchy. Setting Start/End manually is effectively just a filter that doesn’t display the period, and although the year must be selected, the column is not shown and there is no indication of the period.

[sokol92]

The simplest and most effective solution is to add the Financial year to the data source.
For a range of cells, this can be done with a formula, the SQL query is also supplemented.

[Lupp]

@flywire

Data > Group and Outline > Grouping

That feature isn’t related to this kind of “semantical” grouping. It simply groups adjacent rows or columns independent of their content.,

[flywire]

Regardless, it works well off the date without the need to add another data column. Can someone tell me how it works? I’d like to replicate it changing year to Financial Year.

[Lupp]

@flywire

Can someone tell me how it {emphasis by @Lupp} works?

What?

@flywire

I’d like to replicate it changing year to Financial Year.

Sorry. I didn’t understand this, and my AI translator was no help.

[flywire]

Thank you. I understand the pivot table just uses the grouping of date.

@Lupp

[It] What?

Change the grouping from Year to Financial Year.

---

Replicate = to duplicate, copy, or reproduce.

+ change Year to Finacial Year.

[Lupp]

I’m obviously too silly to understand.
How should a tool like the PivotTable supplier know what concept of ‘FinancialYear’ you want to be applied? Tell it using a helper column containing the appropriate formulas. If the supplier would want to look smart, it would also have to ask you how it can deduce the FY from the ordinary date in something like a hidden column, wouldn’t it?
BTW: Do youi know the start of the English tax year?

[flywire]

Grouping on date has a dialog to select sec..year or manual. Behind the dialog is functionality to calculate the year from date. I’m suggesting saving the financial year start (I’m happy to hard-code it) and using that to group the data.

UK Tax History Lesson – How come the UK tax year ends on April 5th?

[Lupp]

How to do it in a spreadsheet-only fashion is demonstrated in the attached example:
disask87988_PivottableRegardingFinancialYear.ods (81.1 KB)

[flywire]

Inserting a calculated Financial Year column is the generally accepted answer for Calc.

[Lupp]

What alternative solutions do you know?

[Villeroy]

Well, Excel’s pivot tables can do this calculation without any extra column. You can add a formula for a calculated field for the pivot table exclusively.