1

I have a pivot table summarizing numeric values per country (say, kilos of pasta consumed per year), sorted in descending order by amount consumed. For reasons, 4 countries represent over 90% of the total consumed, so that the remaining 10% is distributed across the rest in negligible amounts.

In Excel, I was able to Group those other countries into their own category ("Others"), aggregating their consumption, yielding cleaner tables and graphs (my table/graph now had 6 data rows, instead of 20-- one for each of the 4 big countries, the "Others," and the Total), making the whole thing easier to read.

When I try to do the same in LibreOffice, countries get grouped in alphabetical order (rather than by volume), creating a useless mess: I have the Others category, but it doesn't contain the right countries.

In theory, I could do it manually, by adding a column Country2, and using VLOOKUP to flag as Others the smaller countries. But that seems overkill: there should be a way to do it automatically.

Here's what I do and the results I get at each step:

  1. Create the Pivot Table, adding first the Pasta field, and then the Country field

enter image description here

  1. Select the country rows, go to Data, Sort... and sort by Column B, Descending.

enter image description here

  1. Drag-select from Guatemala downwards (so, Rows 8 - 24), click on Data, Group and Outline, Group. (Or press F12, if not bound to Guake, like me).

enter image description here

You'll notice that the countries are back in alphabetical order, though Group 1 was created with Rows 8 - 24, just not how I had them sorted.

2
  • This is difficult to reproduce. Please give an example and explain how you created the Others group in LibreOffice. Also, have you tried putting the volume first in the pivot table layout rather than the country name? The layout order determines the sort order.
    – Jim K
    Commented Jan 25, 2017 at 6:33
  • Done, let me know if I can do anything else to improve the question =). I tried adding the volume first, and still got the same order. I tried 2-3 different "ways," moving different stuff around.
    – Khashir
    Commented Jan 26, 2017 at 15:19

2 Answers 2

0

Ok, here is what I came up with:

pivot table

Below are the options. PASTA (KG) was the first row field but then I replaced it with PASTA (KG)2 which is the grouped field.

pivot table options

To make the grouping, I selected rows 8-24 in column A and pressed F12. Double-clicking on "Group1" collapsed it.

However, it's far from a perfect solution. Adding a column Country2 may be a better approach. Would it really require VLOOKUP though? In my simple test all it would need is a country name, but maybe your actual data is more complex.

Instructions for pivot table grouping are under Advanced DataPilot Use here: https://wiki.openoffice.org/w/images/a/a1/0306CG-CalcDataPilot.pdf

One more note: In Apache OpenOffice I was able to rename the group to "Others," but in LibreOffice this made the group go away.

1
  • Heya, thanks for the answer. Sadly, the imperfections render LibreOffice unsuitable for my needs---I had to switch back to Excel for this particular project. I also ran into the problem you mention about renaming: the solution is to remove and re-add the Field with the renamed group. It seems that when you group, LibreOffice is filtering based on row names, and doesn't track that the renamed group as being the former group. Thanks again for the effort.
    – Khashir
    Commented Feb 3, 2017 at 3:44
0

There is another way, which is described in here. I quote: "Pivot table data can still be sorted in LO6. Edit the pivot table layout, then double-click on the first Row Field. This brings up a dialog that contains the Options button mentioned in @m.a.riosv's answer.".

By doing so, you can select which is the field by which you want data to be sorted out.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .