Bug 47523 - Functionality request for PIVOTTABLE: Add option for Pivot table will count empty cells in single-column data source
Summary: Functionality request for PIVOTTABLE: Add option for Pivot table will count e...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.5 release
Hardware: All All
: medium enhancement
Assignee: Felipe Lema
Depends on:
Blocks: Pivot-Table
  Show dependency treegraph
Reported: 2012-03-19 09:27 UTC by David F Smith
Modified: 2023-01-30 11:24 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

As requested: spreadsheet with data (containing some empty cells) and resulting pivot table. (8.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-06-13 05:18 UTC, David F Smith

Note You need to log in before you can comment on or make changes to this bug.
Description David F Smith 2012-03-19 09:27:18 UTC
I want to use a pivot table to count responses to a survey question, but it ignores blank cells in the answer column.  To see the behavior:
1. Enter the following data in a column


   (That's 4 Yes, 3 No, and 3 blank cells in the column titled Answer.)
2. Select all 11 cells and choose Data | Pivot Table | Create.  "Current
   selection" should be selected.  Click OK.
3. Drag the Answer button to Column Fields and to Data Fields.
4. Click Options, change the function to Count, and click OK.
5. Click OK to create the pivot table.

The resulting table looks like this:
(empty)  No     Yes    Total Result
           3      4       7
The three empty cells have not been counted.

Apologies in advance if I have misunderstood what "empty" means.

(I also submitted this a couple of years ago for OpenOffice.)
Comment 1 David F Smith 2012-05-01 16:41:44 UTC
This issue has not been addressed in more than a month.  Is there any information that I can add?
Comment 2 sasha.libreoffice 2012-06-13 02:05:51 UTC
Thanks for bugreport
Please, attach document produced in description for testing
Comment 3 David F Smith 2012-06-13 05:18:35 UTC
Created attachment 62961 [details]
As requested: spreadsheet with data (containing some empty cells) and resulting pivot table.
Comment 4 sasha.libreoffice 2012-06-14 23:51:48 UTC
Thanks for attachment
Using attachment, reproduced in 3.3.4, 3.5.4 and msExcel 2007
in all cases used right mouse click->Refresh

IMHO it done so for compatibility with Excel. Therefore NOT A BUG

But, may be possible to add some option elsewhere for counting empty cells too
Comment 5 Joel Madero 2012-07-02 21:34:54 UTC
This is a bug. It should definitely count blank cells. I'm marking this as a MINOR bug with LOW priority as I don't think many people are affected but for those who are, correcting it means not using the pivot table or taking extra steps to count blanks. Thanks for reporting this.
Comment 6 Kohei Yoshida 2012-07-09 15:15:44 UTC
Marking this as an enhancement request.
Comment 7 bugquestcontri 2013-05-11 06:26:43 UTC
I wonder why bugs are sometimes marked as "enhancement". IMHO a "bug" is a fault in a current functionality already built into LibO. An "enhancement" is a further improvement of something which is already working but it can be made better. 

Not counting of blanks is IMHO a bug because a pivot table should be able to count blanks.

This very bug might not be important for many users, some might be able to create a work around, but for some it could be show stopper of using LibO.

There is currently again a related question in AskLibO: 
Comment 8 David F Smith 2013-07-18 16:16:27 UTC
For the record, this problem still exists in, tested under Windows 7 Professional (6.1.7601 Service Pack 1).
Comment 9 David F Smith 2015-03-12 04:00:42 UTC
For the record, this problem still exists in Version
(Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432)
under Windows 7 Professional (6.1.7601 Service Pack 1).
Comment 10 David F Smith 2016-04-23 03:31:58 UTC
This problem still exists in v5.1.2.2
under Windows 7 Professional (6.1.7601 Service Pack 1).
Comment 11 David F Smith 2016-09-29 16:23:32 UTC
For what it's worth, I have retested this with version
(Build ID: 8f96e87c890bf8fa77463cd4b640a2312823f3ad)
under Windows 10 (version 10.0.10586)
and it still fails.

The user interface for creating a pivot table has changed (under Insert rather than Data, and different selection of the data function), but the result is the same: empty cells are not counted.
Comment 12 marc 2019-05-01 10:49:27 UTC
Still missing in 6.2.
I vouch the request for enhancement.
Comment 13 Valerio Bozzolan 2021-06-17 11:43:25 UTC
I can reproduce on on Ubuntu 20.04.2 LTS (Focal Fossa).

Sadly, I've seen a customer who had to abandon LibreOffice on 40 workstations because of this bug.

An option like:

 [X] process also empty cell

Was present in another proprietary solution but not LibreOffice.
Comment 14 David F Smith 2021-06-21 02:16:03 UTC
In response to Valerio's Comment 13, please note that the Pivot Table Layout box already has an Option checkbox "Ignore empty rows."  If that option is checked, there is no column in the resulting pivot table headed "(empty)", but even if the option is not checked, blank rows are not counted and not included in the total.

Version: (x64) / LibreOffice Community
Build ID: a529a4fab45b75fefc5b6226684193eb000654f6
CPU threads: 4; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Comment 15 Valerio Bozzolan 2021-08-31 09:42:10 UTC
Trying to be useful I've described the use-case and the steps to reproduce also here:


It could be clearer with screenshots and bows and ribbons.
Comment 16 terrysaunders 2021-09-22 09:44:51 UTC Comment hidden (spam)
Comment 17 Felipe Lema 2021-12-07 01:28:53 UTC
lemme give this one a go

I'll add a "count empty cells" checkbox to the Pivot Table Layout dialog (visually close to the "Ignore empty rows" checkbox) that defaults to "off" (to preserve original behaviour). Let's see how it looks from there.
Comment 18 Felipe Lema 2022-01-11 05:22:57 UTC
Hello there

I've changed the "yes/no ignore empty rows" bool to a "list, ignore or count" enum in the code


I'm having trouble understanding where/how to count the empty rows.

I believe it should be done in `ScDpfilteredCache::fillTable(…)`, but I could use some help understanding the tree structures there (see https://opengrok.libreoffice.org/xref/core/sc/source/core/data/dpfilteredcache.cxx?r=f528fff9#95 )

It doesn't seem documented, so it's feels like I'm rummaging too much around here. And it seems I get more questions than answers (Why trees? Are tree items cells, rows? do row-col represent original data or pivoted data? is "(empty)" added within this method?)

Anyway,a some pointer around this code would me appreciated (mayme counting should me done elsewhere)
Comment 19 Felipe Lema 2022-01-28 03:06:44 UTC
cross-referencing patch for this https://gerrit.libreoffice.org/c/core/+/129088
Comment 20 hobartpainters1 2022-03-12 05:30:01 UTC Comment hidden (spam)
Comment 21 Bob A. 2022-09-21 16:36:06 UTC Comment hidden (spam)
Comment 22 Felipe Lema 2022-09-27 01:41:04 UTC
giving this one a second try
Comment 23 A 2022-11-14 08:20:55 UTC Comment hidden (spam)
Comment 24 Felipe Lema 2022-11-21 02:49:53 UTC
x-ref second patch https://gerrit.libreoffice.org/c/core/+/143012

I kept the combobox in the ui, even though two bools are handled underneath
Comment 25 Felipe Lema 2022-11-28 16:07:12 UTC
anyone know how to properly do a `loext:…`? I'm getting the following error and I don't know how to solve it (where should I add this namespace prefix?)

  Fatal: namespace prefix "loext:count-empty-rows" is not declared

I tried to match adding a loext: the way it was done in https://git.libreoffice.org/core/+/9c2c48f14535e58cad0453fef584400ee703aecc%5E%21
Comment 26 Bob A. 2023-01-30 11:24:01 UTC Comment hidden (spam)