Ask Your Question

[SOLVED] How to group by month AND year

asked 2019-06-17 23:55:41 +0200

kalliklles gravatar image

updated 2019-06-20 16:01:36 +0200

I have a spreadsheet with more than one year of financial transactions and need to group them by month and category with a pivot table.

I do the usual: 1. select my data, then create a pivot table with date for column, category for rows, and amounts for cell data 2. In the pivot table, select one fo the date column headers and the group by month

The problem is that LO groups together the same months of different years, and I find myself with exactly 12 columns, one for all the Januaries combined, one for all the Februaries, and so on.

How can I convince LO to group by month AND year? Do I have to manually construct a MMM-YY string with the date functions in a helper column?

EDIT: A manually constructed helper column distinguishes months from different years, but the month columns are then sorted alphabetically in the pivot table. Definitely not the desired result

2nd EDIT: problem solved by multiple selection in the Group and Outline | Group dialog, as suggested by @robleyd in the comment below

edit retag flag offensive close merge delete



Have you tried selecting month and year when setting up the grouping? Using Data | Group and Outline | Group

robleyd gravatar imagerobleyd ( 2019-06-18 02:44:36 +0200 )edit

I can't believe I didn't try that! To my (weak) defence, the interface seems to suggest the options are exclusive. Selecting both year and month solves the problem

kalliklles gravatar imagekalliklles ( 2019-06-20 15:57:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-06-18 10:09:45 +0200

Lupp gravatar image

updated 2019-06-18 10:49:39 +0200

Best you use a "Pivot Table" (DataPilot tool) directly. It doesn't depend on grouped data.
You may want to study this attached example.
Please note:
-1- PivotTable does not update automatically.
-2- RightClick into any outut cell to get the approppriate context menu.
-3- Default is that PT are created in their own sheets, but you can set a different output target.

(-4- Automatic refresh is omitted for efficiency reasons mainly. There is no option insofar, but you can enforce automatic updates with the help of user code.)

edit flag offensive delete link more


Hmm, that's exactly what I did, perhaps I didn't explain it well enough. The question was precisely how to sort data by Month and Year in a pivot table created with the Data Pilot tool. As @robleyd suggested, selecting both options in the Group and Outline panel solves the problems

kalliklles gravatar imagekalliklles ( 2019-06-20 15:59:31 +0200 )edit

Pivot table is sorted on the order of columns in row fields, so moving the year to the beginning and then the month should work.

m.a.riosv gravatar imagem.a.riosv ( 2019-06-20 20:11:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-06-17 23:55:41 +0200

Seen: 51 times

Last updated: Jun 20