| Author |
Message |
elton123
Newbie
Joined: 29 Jul 2009
Online Status: Offline
Posts: 6
|
  
Topic: How do you merge groups
Posted: 29 Jul 2009 at 1:40pm |
|
I have 2 fields that are exactly the same therefore 2 groups. I would like to return the count of both groups as one. How can this be done?
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Jul 2009 at 2:59pm |
Sorry but I am not sure what you mean by your post. On the dace of it it sounds like you can just do a distinct count but I am not sure.
Can you post sample data and how you want it counted or expalin further? Edited by DBlank - 29 Jul 2009 at 5:11pm
|
| IP Logged |
|
elton123
Newbie
Joined: 29 Jul 2009
Online Status: Offline
Posts: 6
|
Posted: 30 Jul 2009 at 6:46am |
Yes for example field1 values are red, blue, and green. No problem in taking count of field1 (ex: red - 12, blue - 23, etc.)
Field2 values are the same red, blue and green. I want to factor field2 count into the field1 count and show up as one, as in one count for both fields.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jul 2009 at 6:54am |
Still not clear exactly on you set up. I think you mean you have 2 columns in your row that you want to add together and then eventually sum.
You can create a formula field and add the two field together.
Table.field1 + table.field2
Then do a SUM on that formula field.
Am I close to what you are trying to do?
|
| IP Logged |
|
elton123
Newbie
Joined: 29 Jul 2009
Online Status: Offline
Posts: 6
|
Posted: 30 Jul 2009 at 7:18am |
|
In other words, how do I group several fields into just one group and subsequently take the count of that group.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jul 2009 at 7:37am |
You can conditionally count any row using Running Totals.
You can create a formula to look at the two field and give you a final result and then group on the formula field... something like "If field a = field b then field a else field b". Once grouped you can do a count on any field at that group level.
Sorry to be so vague but I just cannot seem to visualize what your row by row data looks like and then what you are doing with it  Again, if you post some actual sample row level data (like an excel spreadsheet view) and then show how you want to count it it would be easier to post a mor especific solution for you.
|
| IP Logged |
|
elton123
Newbie
Joined: 29 Jul 2009
Online Status: Offline
Posts: 6
|
Posted: 30 Jul 2009 at 9:10am |
data looks like this on the report
field 1 field2 field3
------- -------- --------
red blue green
blue blue blue
green red green
what I would like to report are the statistics in the form of a count so it would ultimately show in its final form
red 2
blue 4
green 3 Edited by elton123 - 30 Jul 2009 at 9:12am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jul 2009 at 10:04am |
If you do not have many options (Red, blue and green) then the easiest solution is to create a formula per possible answer and then sum these formulas.
@RedCount as... if field1="red" or field2="red" or field3="red" then 1 else 0
@Bluecount as...if field1="blue" or field2="blue" or field3="blue" then 1 else 0
@Greencount as...if field1="green" or field2="green" or field3="green" then 1 else 0
Do a SUM on each of these 3 formula fields and you will have your 3 totals
EDIT!!!!!!!!!!!!!!!!!------------------------------
I take the above back. tried to answer to quick and didn't think this through. The above will only coun tthe # rows that each color appears in.
Let me think about this a little more Edited by DBlank - 30 Jul 2009 at 12:07pm
|
| IP Logged |
|
elton123
Newbie
Joined: 29 Jul 2009
Online Status: Offline
Posts: 6
|
Posted: 04 Aug 2009 at 7:11pm |
|
ok, would a word count work?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Aug 2009 at 7:48pm |
I apologize elton... I forgot to get back to you on this.
A word count should work. I don't have crystal in front of me to validate this but I think you just need to tweak the other process i suggested a little to make the word count process...
You can use 3 if-then statements per "count" formula, adding then togther fopr a total per row.
From there you can SUM each formula to get your total per for all rows "color".
Example...
@RedCount as....(if field1="red" then 1 else 0) + (if field2="red" then 1 else 0) + (if field3"red" then 1 else 0)
@BlueCount as....(if field1="blue" then 1 else 0) + (if field2="blue" then 1 else 0) + (if field3="blue" then 1 else 0)
etc.
Now you can just SUM @RedCount and that should get you to = 2 from your example data.
Test it for one "color" and see if it works and let me know if you need more help or I led you astray. Edited by DBlank - 04 Aug 2009 at 7:50pm
|
| IP Logged |
|
|
|
