LibreOffice pivot table group by month
I think the problem is that the "
Month" column has different values for each date. So LibreOffice doesn't understand you want to group the output by distinct months.
Thus, a solution could be to "construct" a date based on cell A2, ignoring the day:
Replace the Formula "
=A2" in the "
Month" column by
This way, you will have the same date for every month cell, depending on the original date.
Then, format that column using the pattern "
MMMM" to display the date as "
Now, refresh the Pivot Table or re-create it with "
Month" in "
Column Fields", "
Customers" in "
Row Fields" and "
Sum - Amount" in "
This should yield the desired result.
I'm not sure whether this was possible in LibreOffice that was current in the time when this question was asked but now this is quite easy to do, so if anyone stumbles upon this question this might help:
Using data from the input table (btw. for this last column "Month" is redundant) one should do the following.
Create Pivot table:
- Select the table and from the menu select
Data > Pivot Table > Create...
- In the
Column Fieldsis the
Row Fieldsis the
When the Pivot table is created:
- Select/click on any cell in the row which contains dates
- From the menu select
Data > Group and Outline > Group
Group By > Intervals
Months(or any interval you would like)
Related videos on Youtube
Lennart about 1 year
Im having data like this:
Date Customer Amount Month 09-01-12 A 20 =A2 (formatted so it shows month name) 10-01-12 B 10 =A3 (formatted so it shows month name) 18-01-12 B 25 etc 05-02-12 A 15 etc
I want to use a pivot table to see the total amount per month per customer. e.g.
Customer Jan Feb Total A 20 15 35 B 35 35 Total 55 15 70
I added an extra column to the data, with the month formatted as 'Jan', 'Feb' etc. In the pivot table it gives a column for each date, it does not group by month. So the result is this:
Customer Jan Jan Jan Feb Total A 20 15 35 B 10 25 35 Total 20 10 25 15 70
How do I fix this?
Lennart over 11 yearsThanks, that was the solution. Too bad LibreOffice can't manage those things by itself.
jmiserez about 7 yearsWorks great, but only if the column is formatted as a date (and not "Standard" or as text).
LisaJ over 2 yearsClick on any one cell got it working for me. I alternately highlighted all of the dates (and got Group1 of the whole thing) or had clicked into a non-date cell.