How to group to months in Pivot Table?

[michaldybczak]

In Excel, whenever I have dates, in Pivot Table, they are neatly grouped by months automatically. One can unwrap them to singular dates, but by default, they are grouped.

How do I do this in Libreoffice?

So far, I had to create another column, then create a formula to show me only the number of the month, possibly year and then use this column in the pivot table. This is tiering.

Is there a way to do grouping of months in Calc? If so, how?

And yes, dates are changed to format date, but it doesn’t change anything. Pivot tables in LO show all dates, and there is no grouping.

This is a very common usage, that we have a lot of business data with dates and want to compare or show them by months to assess it. I can’t believe there is no such functionality in LO.

[JohnSUN]

How to properly filter by date in a pivot table?

If you need monthly results: Click any date cell in the pivot menu:Data>Group>Group [F12] Group by month and year. Pivot_Mont_Person_Category.ods (88.3 KB)

[michaldybczak]

Thanks. It took me some time to figure out that I need to find option Data->Group in menu or tab.

Is there a way to change the formatting of the months? Now it shows months as a roman numbers, like XII and so on. I would like to change it to normal numbers or maybe name of the months if that is possible.

[Villeroy]

Why the month abbreviation of date format in Polish language is Roman numerals?

Libreoffice uses Roman numerals for MMM month abbreviation when formatting dates: [obraz] I am using: Version: 7.5.5.2 (X86_64) / LibreOffice Community Build ID: 50(Build:2) CPU threads: 8; OS: Linux 6.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: pl-PL (pl_PL.UTF-8); UI: pl-PL 7.5.5-1 Calc: threaded The LC_TIME locale category for pl_PL.UTF-8 is: $ locale -k LC_TIME abday="nie;pon;wto;śro;czw;pią;sob" day="niedziela;poniedziałek;wtorek;środa;czwartek;piątek;sobota" abmon="sty;lut;ma…

[michaldybczak]

I’m not sure if that is the same issue, but it is related. Nowadays, I see alternate date formatting with normal numbers, but roman letters remained on pivot tables and I don’t see a way to change that.

If there are numbers in pivot table, I cannot right-click them and choose formatting, but I still can change formatting when clicking on proper buttons/tools. When I used ctrl+1 shortcut, it worked and I could change the date formatting, but it had no influence on the pivot table. This is inconsistent. We should be able to format pivot tables, especially if it doesn’t follow the source table formatting in this case.

By the way, dates are horrible in LO, because LO often treats some numbers as dates, even when different format is selected. The only fix is to change them to text and add ’ at the beginning to force LO not to apply any automatic changes. But then, you cannot calculate that cell… This is crazy, but not a focus of this topic, so ignore it ;).