Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks, that was the solution. Too bad LibreOffice can't manage those things by itself. –  Lennart Jul 19 '12 at 8:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.