Friday, November 25, 2016

New pivot table function in Calc: Median

After have some work with a pivot table related performance issue, I've got a request to implement a new function for pivot tables. It seemed a useful feature to have and also an easy thing to implement at the first sight.

Pivot table functions

Pivot tables are used to analyse a larger amount of data using different statistic functions for that. Both LibreOffice Calc and Microsoft Office Excel have the same function palette with 11 functions like average, sum, count and so on. These aggregate functions can be used for data fields and for row/column fields. Data fields determines which source field would be summarized and which function would be used for that. Row/column fields don't have a function by default, but with setting one user can calculate subtotals too.


Why median? In psychology research pivot tables can be a useful thing to analyze data of the participants. It depends on the type of the data which functions can be used for aggregation. When we have interval variables we can use average, but for ordinal variables we would need median, which was missing from the function list.

This was the starting point, but I've also found some user posts about missing this feature from MS Excel (see links below). It seems Excel users had to face the very same problem again and again over the last ten years. Well, it has some advantages if a software is open source, I guess.

Good news

In LibreOffice 5.3, median is available for pivot tables:


... to my professor, Attila Krajcsi (Department of Cognitive Psychology, Eötvös Loránd University) for supporting the LibreOffice project, with the idea to have this new function and also with some course credits!