Pivot Tables - Monthly subtotals

users
#1

Hello,

I have data like the following:

Transaction Date | Object | Transaction
2021-01-01 | Car | Bought
2021-03-01 | Car | Bought
2021-03-15 | Truck | Bought
2021-03-16 | Car | Bought
2021-03-18 | Car | Sold
2021-04-01 | Truck | Sold

I want to create a pivot table with Transaction Date as columns, Object as rows, and counts of Transaction as data fields . However, I do not want individual dates as columns, I want to look at the data by month. In other words, instead of this:

Object | 2021-01-01 | 2021-03-01 | 2021-03-15 | 2021-03-16 | 2021-03-18

2021-04-01

Car | 1 | 1 | 0 | 1 | 1 |
Truck | 0 | 0 | 1 | 0 | 0 | 1

I want this:

Object | January | February | March | April

Car | 1 | 0 | 3 | 0

Truck | 0 | 0 | 1 | 1

How do I do this in LibreOffice Calc?

Julian

#2

Well, the simple way.
Select all of your Date cells with data and change the Cell format to
display only the month portion of the date.
When you create the pivot table, include the 'Transaction Date' column
in both the Columns fields list and the Row fields list, along with
Object.
You could also set the Filters list to include Object if you wanted to
look at Cars or Trucks.

I hope that works for you, best wishes,

Drew

#3

Actually, I figured out how to get what I wanted. This is the way:

1. Build the pivot table with Transaction Date as rows, Object as
    columns, and counts of Transacations as data fields
2. Click any date in the Transaction Date column
3. Date | Group and Outline | Group
4. Select the "Intervals" radio button under "Group By"
5. Check the "Months" checkboxes under "Group By"

This will group transaction data by month. If there are data from multiple years, then also check the "Years" checkboxes under "Group By" in the Group dialog box. This will avoid group data from every January from every year in the same row (if that's not what you want).

What was getting me stuck was that the Transaction Date must be in the rows field. You cannot do this with Transaction Date in the columns field, as I wanted to.

Julian

#4

Howdy,

Excellent!

Turns out I was so intent on trying to force that row to column header
trick I gave you a bogus answer anyway.

Glad you found a solution.

Best wishes

Drew