| Author |
Message |
swatts
Groupie
Joined: 17 Mar 2010
Online Status: Offline
Posts: 83
|
   Topic: Wild sorting question.
Posted: 10 Sep 2010 at 4:00am |
|
My boss has a table that sort of looks like this:
Jane Doe 1
Jane Doe 200
Jane Doe 1
Jane Doe 1
Jane Doe 305
Mike Doe 1
Mike Doe 1
Steven Doe 1
Steven Doe 1
Roger Doe 302
I need to sort it where it gets only the people that have a 1. If like Jane Doe has a 1 and a 200, I do not want her. My results would be:
Mike Doe 1
Steven Doe 1
Does this make sense?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Sep 2010 at 4:42am |
group on the name
create 2 insert summaries at the group 1 (name) level
SUM(numberfield,namefield)
Count(numberfield,namefield)
In the select expert change it to a group select criteria
SUM(numberfield,namefield)=Count(numberfield,namefield)
|
|
IP Logged |
|
swatts
Groupie
Joined: 17 Mar 2010
Online Status: Offline
Posts: 83
|
Posted: 10 Sep 2010 at 7:18am |
|
Doing summaries is very new to me. I found "count" in the "Calculate This Summery" drop down, but I cannot locate a "SUM."
There is a Maximum, Minimum, Count, Distinct Count, Nth Largest N is, etc..
Am I in the right place?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Sep 2010 at 8:58am |
yep you are on the right track
this means that the field is a string and not numeric
create a formula field to convert it to a number then use the formula for summing
tonumber(numberfield)
|
|
IP Logged |
|
swatts
Groupie
Joined: 17 Mar 2010
Online Status: Offline
Posts: 83
|
Posted: 10 Sep 2010 at 9:01am |
|
Can do! Thanks for your expertise
|
|
IP Logged |
|
swatts
Groupie
Joined: 17 Mar 2010
Online Status: Offline
Posts: 83
|
Posted: 14 Sep 2010 at 4:54am |
|
I cannot change the namefield to a number. This is what it actually looks like:
Field_Name:
Josh Barnett
Rachel Word
Tammy Long
Source_of_Pay:
1
2
1
1
3
Sometimes Josh will pay with 1. He may have done it 50 times and then paid 3 times with #3. However, I am trying to figure out who has paid with only 1 ever and how many customers we have that has done this?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Sep 2010 at 4:59am |
Change the Source of pay to a number field (assuming it is a string)
Then follow my first post to get the data as yyou wanted.
|
|
IP Logged |
|
swatts
Groupie
Joined: 17 Mar 2010
Online Status: Offline
Posts: 83
|
Posted: 14 Sep 2010 at 5:16am |
I sort of understand your first post and the number field is a number but when you posted: SUM(numberfield,namefield)
Count(numberfield,namefield)
What is the meaning of namefield in this summary?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Sep 2010 at 5:21am |
you can do counting at a group level by adding it as a condition
namefield is a place holder for your field that is the "name" you are grouping on.. i think you referenced later as "Field_Name"
Edited by DBlank - 14 Sep 2010 at 5:21am
|
|
IP Logged |
|
swatts
Groupie
Joined: 17 Mar 2010
Online Status: Offline
Posts: 83
|
Posted: 14 Sep 2010 at 5:40am |
|
Is there anyway to do this with a formula?
|
|
IP Logged |
|
|
|
