LibreOffice pivot table group by month

libreoffice-calcopenoffice-calcpivot table

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?

Best Answer

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

    =DATE(YEAR(A2),MONTH(A2),"1")

    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 "January".

  • Now, refresh the Pivot Table or re-create it with "Month" in "Column Fields", "Customers" in "Row Fields" and "Sum - Amount" in "Data Fields".

This should yield the desired result.

Related Question