Show 0 (zero) in pivot table

Discuss the spreadsheet application

Show 0 (zero) in pivot table

Postby eponymous » Wed Oct 04, 2017 8:20 pm

I've got a pivot table which is using "count" to display the count of various items in a column. Where the count is 0 (zero), the table has a blank cell, instead of the number 0.

How can I change that?
OpenOffice 5.4.1.2 on Mac OS X 10.11
eponymous
 
Posts: 6
Joined: Wed Oct 04, 2017 8:17 pm

Re: Show 0 (zero) in pivot table

Postby Zizi64 » Thu Oct 05, 2017 7:03 am

The function COUNT() and COUNTA() will count the non empty cells. Type-in zeros into cells if you want to count them (if you want to add the numbers of the zeros to the result).


And there is a option named "Show zero values" in the
Tools - Options - Open/LibreOffice Calc - View menu item.
Try to check-in it, if you want show the earo values of the results of the formulas.
Last edited by Zizi64 on Thu Oct 05, 2017 8:08 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5911
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Show 0 (zero) in pivot table

Postby Villeroy » Thu Oct 05, 2017 8:02 am

The numbers in the data fields show the results for combined category values in row/column fields.
If a combination of row/column values does not exist in the source table it would be wrong to show a zero. The sum/count/whatever is not zero when it is missing.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24400
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Show 0 (zero) in pivot table

Postby eponymous » Thu Oct 05, 2017 4:43 pm

Villeroy wrote:The numbers in the data fields show the results for combined category values in row/column fields.
If a combination of row/column values does not exist in the source table it would be wrong to show a zero. The sum/count/whatever is not zero when it is missing.


Actually that is precisely the definition of zero when counting. I can understand why in some cases a blank cell might be useful, but I want 0 when the count is 0. Note that Excel has this via a pivot table preference for "Empty cells as…" (see image). Frankly I don't quite understand why the default isn't 0, but at least there's the option.

As a practical example (and the precise case I'm using it for), if you're keeping track of box scores in softball games and you have a column for outcome with values of "W", "L" and "T" (ties are allowed), you can use a pivot table to create the standings. In this case, if a team has all "W", you want the table to read 0 under "L" and "T". And that's the logical behavior. What's the count of "L" for that team? It's not undefined; it's zero.
Attachments
Screenshot 2017-10-05 10.34.09.png
OpenOffice 5.4.1.2 on Mac OS X 10.11
eponymous
 
Posts: 6
Joined: Wed Oct 04, 2017 8:17 pm

Re: Show 0 (zero) in pivot table

Postby eponymous » Thu Oct 05, 2017 4:50 pm

Zizi64 wrote:The function COUNT() and COUNTA() will count the non empty cells. Type-in zeros into cells if you want to count them (if you want to add the numbers of the zeros to the result).


I understand about count and non-empty cells, but in many cases (see my reply to the other comment), you want to be able to show a zero count in a pivot table and not just have a blank cell.

Zizi64 wrote:And there is a option named "Show zero values" in the
Tools - Options - Open/LibreOffice Calc - View menu item.
Try to check-in it, if you want show the earo values of the results of the formulas.


I'm not finding this. I see "Zero values" (which I already have checked) under Preferences > LibreOffice Calc > View > Display section

Is that what you mean?
OpenOffice 5.4.1.2 on Mac OS X 10.11
eponymous
 
Posts: 6
Joined: Wed Oct 04, 2017 8:17 pm

Re: Show 0 (zero) in pivot table

Postby Zizi64 » Thu Oct 05, 2017 7:13 pm

I see "Zero values" (which I already have checked) under Preferences > LibreOffice Calc > View > Display section

Is that what you mean?


Yes. I see now you are using Mac.(That op.sys has a different menu structure...)
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5911
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Show 0 (zero) in pivot table

Postby MrProgrammer » Thu Oct 05, 2017 9:39 pm

Hi, and welcome to the forum.

eponymous wrote:I've got a pivot table which is using "count" to display the count of various items in a column. Where the count is 0 (zero), the table has a blank cell, instead of the number 0.
Type 0 in some unused cell and press Enter. Edit → Cut. Select the cells in the pivot table which contain the numeric counts. Edit → Paste Special → Selection=Numbers → Options=(none) → Operations=Add → Shift=Don't → OK.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3077
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Show 0 (zero) in pivot table

Postby eponymous » Thu Oct 05, 2017 10:07 pm

MrProgrammer wrote:Hi, and welcome to the forum.

eponymous wrote:I've got a pivot table which is using "count" to display the count of various items in a column. Where the count is 0 (zero), the table has a blank cell, instead of the number 0.
Type 0 in some unused cell and press Enter. Edit → Cut. Select the cells in the pivot table which contain the numeric counts. Edit → Paste Special → Selection=Numbers → Options=(none) → Operations=Add → Shift=Don't → OK.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.


That's not really a solution so much as a surrender.

I think I'll file a feature request for this.
OpenOffice 5.4.1.2 on Mac OS X 10.11
eponymous
 
Posts: 6
Joined: Wed Oct 04, 2017 8:17 pm

Re: Show 0 (zero) in pivot table

Postby keme » Fri Oct 06, 2017 1:26 am

For the softball example, the selection of teams and outcomes would be static through the "life" of the list (a season). In this situation, a workaround may be to use COUNTIFS() within a predefined table structure, instead of building a dynamic summary as a pivot table. One advantage with formulas is that they are "live". You don't need to manually refresh. Getting the table sorted according to standing may constitute more work with that solution, though.

Is it possible to attach here a file with your source data, or if that is confidential, some dummy data with the same layout?
User avatar
keme
Volunteer
 
Posts: 2598
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Show 0 (zero) in pivot table

Postby eponymous » Fri Oct 06, 2017 2:09 am

keme wrote:For the softball example, the selection of teams and outcomes would be static through the "life" of the list (a season). In this situation, a workaround may be to use COUNTIFS() within a predefined table structure, instead of building a dynamic summary as a pivot table. One advantage with formulas is that they are "live". You don't need to manually refresh. Getting the table sorted according to standing may constitute more work with that solution, though.

Is it possible to attach here a file with your source data, or if that is confidential, some dummy data with the same layout?


Here's the actual data with no team name or anything identifiable.

I get that there might be workarounds, but my broader point is that pivot tables should provide this option.
Attachments
demo.ods
(23.76 KiB) Downloaded 7 times
OpenOffice 5.4.1.2 on Mac OS X 10.11
eponymous
 
Posts: 6
Joined: Wed Oct 04, 2017 8:17 pm

Re: Show 0 (zero) in pivot table

Postby keme » Fri Oct 06, 2017 10:48 am

While I do agree that "missing=zero" should be available, and even that it be the default for counting, I think the static list is better for this particular use.
One additional advantage is that you can use the static list for input validation, which helps to avoid those leading/trailing spaces and other typos. See attached edit of your file.
Attachments
demo.ods
(18.43 KiB) Downloaded 6 times
User avatar
keme
Volunteer
 
Posts: 2598
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Show 0 (zero) in pivot table

Postby eponymous » Sat Oct 07, 2017 12:00 am

I see what you mean, but I do more with the pivot table than I showed. Once the names are in there once, the typo problem goes away with autocomplete.
OpenOffice 5.4.1.2 on Mac OS X 10.11
eponymous
 
Posts: 6
Joined: Wed Oct 04, 2017 8:17 pm


Return to Calc

Who is online

Users browsing this forum: Zizi64 and 37 guests