[Solved] Count items with nonzero values

Discuss the spreadsheet application
Post Reply
Burnart
Posts: 8
Joined: Thu Apr 05, 2018 12:37 am

[Solved] Count items with nonzero values

Post by Burnart »

The other day I posted this viewtopic.php?f=9&t=105437 that all got resolved which was great but then I realised I was on the wrong track about what was required. :crazy:

Here's something like my previous simple example:
City Number
Brisbane 12
Geelong 0
Darwin 3
Adelaide 2
Adelaide 7
Geelong 5
Brisbane 0
Geelong 2
Geelong 0
Darwin 0
Adelaide 3
Brisbane 1
Darwin 10
Geelong 0

What I need to do is count the number of times per city the Number result is greater than 0. For Adelaide in this example the answer would be 3 - all three Adelaide entries are above 0. For Geelong the answer would be 1 - it has four entries but only one above 0.

I'm not sure if this is still in spreadsheet territory or whether it crosses over into database manipulation. I can describe it in the following terms (NOT a formula): IF (CityNumber >0) Then (X+1) ........ where X is a variable associated with a specific City. The solution would I presume use a different variable for each City. In the actual data I need to manipulate there are over 100 "City" names.

Another way to think about this might be to cull any row where the Number is 0 and then count the amount of times each City still appears in the list. I think I can work that out on my own but it's a bit of a clunky work around (a bunch of "City" names will disappear from the list) so if anyone has any better ideas I'd be appreciative of the help.
Last edited by robleyd on Wed Jun 16, 2021 4:01 am, edited 2 times in total.
Reason: Tag [Solved]
OpenOffice 4.1.1 on Windows Vista
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: counting occurences

Post by robleyd »

A Pivot Table is still the answer; use a filter in the pivot table to exclude entries where the value is not what you want, and use the Count option on the data field rather than Sum. I've added a new pivot table based on your new data to the sample I provided in the other topic - see below.

As a Croweater, I'm pleased to see Adelaide scores highest :bravo:
Attachments
simple_pivot.ods
(10.24 KiB) Downloaded 153 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Burnart
Posts: 8
Joined: Thu Apr 05, 2018 12:37 am

Re: Counting occurences

Post by Burnart »

Hello robleyd - looks like a solution. Obviously I need to spend more time with Pivot Tables. I'm not in the office today so I will have to check it out next time I'm there - ie. Tuesday. I do use spreadsheets often but I mostly just use them as a means to sort information and do basic calculations. When I get into situations were I'm trying to extract specific kinds of data that's when I feel like I'm bashing my head against a wall. It's great that people such as yourself are willing to use their expertise to help out. Thanks.

Ha Ha! - this shows why I need to use a spreadsheet I couldn't even add up the Geelong count in my simple sample right!

Should have had Murbko on the list - how is the river today, lots of water flowing down from recent rains?
OpenOffice 4.1.1 on Windows Vista
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Counting occurences

Post by robleyd »

Shirley you jest about the water flow :lol: It'll take forever to get here - especially if there is any left after it goes through cotton country. We haven't had decent flows since the high water in Dec 16.

You might find Using the DataPilot [PDF] a handy reference.

Enjoy your long weekend.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Burnart
Posts: 8
Joined: Thu Apr 05, 2018 12:37 am

Re: Counting occurences

Post by Burnart »

I've had a chance to look at the new version of the Pivot Table and it does work but it's too clever! If a location has no positive numbers amongst it's entries it's being dropped from the list but it would be more useful if it was reported as having a "0" outcome. In the actual data I'm trying to manipulate it turns out there are a lot more with "0" locations than I would have predicted. 58 different locations in the "solved" list but 102 different locations in the "original" list. (I have 1500 data rows associated with the 102 different locations.) If there is an approach to the Pivot Table that will solve this I'm not seeing it. I tried adding additional filters but couldn't get it to work.

Thinking about it in slightly different terms if I had a new spreadsheet with a list of all the different "original" locations and then imported/merged the solved data could I use the location name as an "id" column to line them up? In that way the "solved" data could be appended as a new column to the correct original location list and all the unreported original locations would remain blank - easy enough to make those read "0".

(Yes it does take a while to get from the top of the river to the mouth - 34 days according to a map I have. Cotton - in this country? Crazy!)
OpenOffice 4.1.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Counting occurences

Post by Villeroy »

Burnart wrote: If a location has no positive numbers amongst it's entries it's being dropped from the list ...

You refer to the second pivot with the filters? Add a calculated column to the source table where missing or negative data are transfered to zeroes, for instance =B20*(B20>0) and use that as the pivot's data field.
 Edit: Even easier: choose Count -- City as data field and remove the filter. This counts the occurances of all cities regardless of the numbers. 
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
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Counting occurences

Post by MrProgrammer »

Burnart wrote:If a location has no positive numbers amongst it's entries it's being dropped from the list but it would be more useful if it was reported as having a "0" outcome.
I'm not sure I understand what you want, but you can use an auxillary column to test if the number is zero or not, than use this as an additional category for the pivot table. You can either list the zero entries separately, or group them. Both methods are shown in the attachment. Right-click on the pivot table → Edit Layout will show you how it was configured.
202106121307.ods
(21.48 KiB) Downloaded 103 times
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
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).
Burnart
Posts: 8
Joined: Thu Apr 05, 2018 12:37 am

Re: Count items with nonzero values

Post by Burnart »

The first question in this thread is definitely solved. The follow ups - not so sure but I discovered that in the end I won't need the zero results specified in the spreadsheet. Without going into the full details, the data I'm sorting is for attaching to some geo-referenced points on a map. The mapping software will let me attach an additional data column using the list of locations as a means to align the required information. It happily skips any locations that aren't in the column I want to append so all the positive results will be attached to the correct location. Any locations which don't get the new data will therefore be the zero attribute locations.

Thanks for all the help and suggestions. :bravo:
OpenOffice 4.1.1 on Windows Vista
Post Reply