Show 0 (zero) in pivot table
Show 0 (zero) in pivot table
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?
How can I change that?
OpenOffice 5.4.1.2 on Mac OS X 10.11
Re: Show 0 (zero) in pivot table
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.
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Show 0 (zero) in pivot table
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.
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Show 0 (zero) in pivot table
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.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.
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.
OpenOffice 5.4.1.2 on Mac OS X 10.11
Re: Show 0 (zero) in pivot table
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: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'm not finding this. I see "Zero values" (which I already have checked) under Preferences > LibreOffice Calc > View > Display sectionZizi64 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.
Is that what you mean?
OpenOffice 5.4.1.2 on Mac OS X 10.11
Re: Show 0 (zero) in pivot table
Yes. I see now you are using Mac.(That op.sys has a different menu structure...)I see "Zero values" (which I already have checked) under Preferences > LibreOffice Calc > View > Display section
Is that what you mean?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
- MrProgrammer
- Moderator
- Posts: 4906
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Show 0 (zero) in pivot table
Hi, and welcome to the forum.
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.
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.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.
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
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Show 0 (zero) in pivot table
That's not really a solution so much as a surrender.MrProgrammer wrote:Hi, and welcome to the forum.
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.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.
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.
I think I'll file a feature request for this.
OpenOffice 5.4.1.2 on Mac OS X 10.11
Re: Show 0 (zero) in pivot table
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?
Is it possible to attach here a file with your source data, or if that is confidential, some dummy data with the same layout?
Re: Show 0 (zero) in pivot table
Here's the actual data with no team name or anything identifiable.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?
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 134 times
OpenOffice 5.4.1.2 on Mac OS X 10.11
Re: Show 0 (zero) in pivot table
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.
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 122 times
Re: Show 0 (zero) in pivot table
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