Friday, November 29, 2019

Filter by months and years in Calc pivot table

I have a huge spreadsheet with many data about work days and hours of special construction machinery with a date of work. There is a pivot table created from that data, where I can see number of hours for every object by type of the machinery. And I need to have a filter by month and years in the Calc pivot table.
But I got a problem when I wanted make a filter using normal pivot table control functionality. I moved Date field to Page fields area: 
and I had a filter by Date in Pivot table, but...
I can't select monts or years there, only days! I want have opportunity like in the Autofilter:
Can I get it in the Pivot table? Yes (nearly)! There are several methods for it.
First, I can add additional columns to the source table, one for month number with formula like =MONTHS(<link_to_cell_with_date>) and second for years with formula like =YEAR(<link_to_cell_with_date>). And I'll have the source table like this:
Then I should change a source in my Pivot table's properties dialog and drag Mounth and Year items to Page fields area. And I have next:
It works fine, I can select months (one or several) and years (one or several), but can I make it without any additional column in my source table? Yes, I can.
Second method is creating of filters by months and years using Pivot table's parameters and grouping.
Open the Pivot table's properties dialog and drag Date item from Page field area to Row fields area. Note, the Pivot table became very unusable, but that action was need us only for some magic. Then select any cell with date in Pivot table and press F12 key (or use menu Data->Group and Outline->Group). It will open a Grouping dialog:
Check Months and Years items there and press OK button. The Pivot table became less unusable ;-) Then need to open Pivot table's properties dialog and drag Date and Years items from Row fields area to Page fields area and close the dialog. Now I have my filters with months and years:
I see some problems in the appearance of filters in second method. 
I can't change name Date to Months! Interesting than I can change filed name in MS Excel in that case. MS Excel allows to set a custom name for the field. 
And both filters have some boundary values like <11/01/2017 and >04/05/2019. I don't know why does it need, but they are.
The second method looks very strange and not obvious. In general that actions is a full copy from MS Excel and I don't like a process, but I need a result of that magic.
The same Russian post is here.

1 comment:

  1. Frederik6:56 PM

    Doesn't work for me. I need to filter something like "newer than 10/2022". In the first method I can only filter by year and month independent of each other.
    The second method doesn't work for me. I use the date as the x axis for a plot so it is a row any way. If I apply your method I cannot filter the way I want to.

    ReplyDelete

All comments will be preliminary check