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 Answer Yes Yes No Yes No No Yes (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.)
This issue has not been addressed in more than a month. Is there any information that I can add?
Thanks for bugreport Please, attach document produced in description for testing
Created attachment 62961 [details] As requested: spreadsheet with data (containing some empty cells) and resulting pivot table.
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
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.
Marking this as an enhancement request.
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: http://ask.libreoffice.org/en/question/17316/pivot-table-empty-line-field/?answer=17341#post-id-17341
For the record, this problem still exists in 4.0.4.2, tested under Windows 7 Professional (6.1.7601 Service Pack 1).
For the record, this problem still exists in Version 4.4.1.2 (Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432) under Windows 7 Professional (6.1.7601 Service Pack 1).
This problem still exists in v5.1.2.2 under Windows 7 Professional (6.1.7601 Service Pack 1).
For what it's worth, I have retested this with version 5.2.2.2 (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.
Still missing in 6.2. I vouch the request for enhancement.
I can reproduce on 6.4.7.2 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.
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: 7.1.4.2 (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
Trying to be useful I've described the use-case and the steps to reproduce also here: https://stackoverflow.com/questions/68019239/libreoffice-calc-and-pivot-table-with-empty-cells It could be clearer with screenshots and bows and ribbons.
This is exactly the conditions I was facing last few months. Thanks for sharing the bug https://www.cleaningservicesmackay.com/house-cleaning
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.
Hello there I've changed the "yes/no ignore empty rows" bool to a "list, ignore or count" enum in the code However 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)
cross-referencing patch for this https://gerrit.libreoffice.org/c/core/+/129088
This is great, I strongly agree! https://www.hobartpainters.com/house-painting https://www.wollongongbathroom.com
Is there an update? Please visit us if while you are waiting for more info. https://www.fencingandgatesballarat.com/colorbond-fencing.html
giving this one a second try
Thanks for this!! https://www.kingsconcretepros.com.au/concrete-slab
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
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
This is great, thank you for sharing https://www.fencingandgatesballarat.com