Jump to content
Sign in to follow this  
lonesomejubilee

Counting different categories in one field... is there a better way?

Recommended Posts

Hello,

I have a field which has a drop down box to select a category, let's say "red", "blue", "green", etc. Each record requires this field to have a selection. I want to count how many times each color was selected. I have hundreds of thousands of records, each with a color or category selected. Currently I do this by using the PatterCount function. It works but it is very, very cumbersome... as I have about 30 different "colors" to choose from so I have to make an individual PatternCount field to summarize each one individually.

Secondly, what is the best way to do the "counting" in another table so I don't have some many fields in my primary table?

At first I was thinking using a Summary field, but that only summarizes the entirety of the field, not individual categories within a field... so that's why I went with patterncount.

Thanks in advance...

LJ

Share this post


Link to post
Share on other sites

You can use a summary field and create a subsummary report. Group it by the category field. The summary field would be a "count" of that field. Then on your report layout, you can put the summary field next to the category field in the Subsummary part. Delete the body part as you dont really need it for the report.

As another option, you could store the selection choices in another table, create a relationship between the two tables, and then use a calculation field with an aggegrate function of Count () but in your case I think the easier way to go maybe the subsummary report.

Share this post


Link to post
Share on other sites

Hi, I don't really understand what you mean, as I understand, you can't make a summary report out of non-numerical fields, and my category or "colors" field is text, not numbers. So how would I go about creating a summary field out of that?

THanks.

Share this post


Link to post
Share on other sites

Not sure how you're using PatternCount here. But summary fields are the standard way to do this. If you sort by color and put a Sub-Summary part in your layout you can get a list of all the colors and their counts with only one field.

Another solution may be a custom function. Does this help?

Share this post


Link to post
Share on other sites

Hi,

You must forgive me because I guess I am not very apt at explaining what I needed...

In your example it will count the total number of possibilities... i.e. the count is always 13, but I want to know the total number of times each color was selected... or maybe I'm not doing it right.

Imagine that drop down field as part of a larger set of fields in a table, it's like I want to count within - within a group.

The Count of summary thing counts the current number (13), but not individually..

Share this post


Link to post
Share on other sites

David,

Just to let you know how I used PatternCount....

I have a field that says PatternCount (Field; "Red")

.. which returns 1 if the selection is red... then I do a summary field of the patterncount field...

and it counts up all the times Red was selected. Like I said, it's the long way around and it requires both the count and sum fields for each possible choice... in my case around 30 so you can see how I'd want a smaller solution.

LJ

Share this post


Link to post
Share on other sites

Go to Scripts and run the "generate report" script to generate the summary report.

Share this post


Link to post
Share on other sites

Thanks Mr. Vodka, it works now, but if I wanted to take it one step further and run that same report, but by person... how would I do that? In other words, individually by person... seeing how many times they selected each color? I know I ask alot and thanks for your patience

LJ

Share this post


Link to post
Share on other sites

Thank you, thank you, thank you... for all your help! Duh... the reason my thing wasn't working out was so stupid and simple... I wasn't sorting the found set in one area, and then I didn't match the break field with what I was sorting by. Thanks for all your help and support...

Now I can get my report done! Yippee!!!

THE LONESOME JUBILEE...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.