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 byThis 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.