Counting and Charts 1

[sbalun]

Wow. Seems like a lot of us Crystal newbies are having issues with counting. Well here's my problem. I hope some of you smarter people can help me with this.

I have a report field named {Respondent.q1}. The possible values in that field are 1,2,3,4, or 5. These numbers represent the answers to q1. Each record in my database represents one persons answers.

I need a formula that will count ONLY the number of 4's and 5's in column {Respondent.q1}. I then want to be able to display the results of that calculation in a bar chart that shows the respondents who answered 4 or 5 as a percentage of all the people who answered that question.

Example: 100 people answered q1. Of those, 40 people answered 4 and 20 people answered 5. So I would have a bar graph with a single bar going from 0 to 60%.

I'm very new at crystal and I'm unsure how to proceed at this point. Help.

 

[noxum]

Are you only interested in the one field {Respondent.q1} or are there more question fields?

 

[sbalun]

I'm only interested in graphing the one question right now. There will be other questions I want to graph but I figured I need to learn to crawl before I walk.

 

[noxum]

I would start out with question one doing the following...

Try grouping by the {Respondent.q1} field. This will group all your answers together for q1:

1
1
1
1
1
1
1...
...
4
4
4
4
4
4...
..and so on

After that get a Count({Respondent.q1}) for each group giving you the total for each answer(1,2,3,4,5) of q1.

You can also select the Show Percentage Of option when you do your count. This will show what percentage each count makes of the total.

If you wanted to group the 4's and 5's together you could use the group in a "Specified Order" option, rather then ascending, when you create your group. Tell Crystal to put 4's and 5's together and leave the other answers in their own group (under the others tab).
You would then see these groups

1 {Total Count or Percentage}
...
2 {Total Count or Percentage}
...
3 {Total Count or Percentage}
...
4's and 5's {Total Count or Percentage}
...


You should be able to then create your bar chart.

 

[sbalun]

Alright...now I'm getting somewhere. Thank you, thank you, thank you.

A couple more questions if you don't mind. The graph I have is showing all the groups. I'd like to only show the 4's and 5's group on the chart. I'm not sure how to hide the data for the other numbers. I've poked around for the right selection to hide those numbers but Crystal has so many options I don't think I'm looking in the right place.

How do I only show the 4's and 5's group in the chart?

Thanks again for your help.

 

[sbalun]

I can suppress the numbers I don't want to show in the details section of the report but they still show up in my chart.

Is there someway to suppress the numbers you don't want to see in the chart the way you suppress them in the details section?

Or alternatively, is there a way to define a group that only contains the 4s and 5s from my previous grouping?

 

[lbass]

Go to chart expert->data and click on your 4's and 5's group, select "order", and assuming you used "specified order" to create your 4's and 5's group, there will be a tab behind "specified order" called "others." Select "others" and "discard all others." This will leave only the one group for your chart.

-LB

 

[sbalun]

Slowly but surely...

If I select "discard all others" on the other tab, indeed, my chart only shows the 4 and 5's group, but I want it to show the percentage of that group compared to all the responses.

Of the people who answered that question, 70% of them answered 4 or 5. But when I discard all others and my chart shows only my named group, it is showing as 100%. That's got to be because I've discarded all the others. I'd like it to only show the 70% of those that answered 4 or 5 compared to everyone who answered the question.

Simply, I want to show the percentage of people who answered 4 or 5 compared to anyone who anwered at all.

 

[sbalun]

<bump>

Anyone? Help?

 

[noxum]

Here is one way...
You could create a formula and Chart off that forumla:

{@4_5%}//Formula
if {Group Name} = &quot;4 and 5&quot; then
PercentofCount(Answer, {Group})

Then Chart this by going into the advanced options of the chart expert. You will see the other question but only the 4 and 5 group will have a value to chart.

noxum

 

[sbalun]

Noxum,

Thank you. You seem to be steering me in the right direction, but I wonder if you could clarify what you've suggested. I'm a little unsure of some of what you have in the formula you suggest. Could you be a little more specific in your explanation of the formula.

Thanks again.

 

[noxum]

{@4_5%}//This is the name of the formula (I'm sure you know that :> )

if {Group Name}//This is the group name. For instance you've probably grouped on the Question number and sorted in a specified order and named to group you wanted something like &quot;4's and 5's&quot;
= &quot;4 and 5&quot; then we want to check for that group name and return the precentage count for that group.
PercentofCount(Answer, {Group}) This is the formula that is created when you do your precentage calculation. You should see it in your list of formulas under that Group number

 

[sbalun]

Ok...almost there. I'm beginning to feel like what I want to do can't be done. I hope I'm wrong.

I've got the formula you supplied to work properly. Indeed, it's only counting the 2 and 3's (I'd been calling them 4 and 5's but they're really 2 and 3's)

The last issue I'm still having is when the graph is displayed.

The graph has a place indicated for &quot;Others&quot; from my grouping. I do not want to show that. But if I &quot;discard all others&quot; in the group selection so they won't show on my graph, then the percentage calculation does not work properly. The only thing that shows are the 2 and 3's but they show up at 100%. And indeed they should because I've told Crystal to ignore all the others when calculating percentage. Arrgghh! This is where the problem is.

I'd like for the graph to appear like it is in Screen Shot 01 below, except, to only show the 60 percent for those respondents that said 2 or 3.

Please take a look at these screen shots. I think they will help you understand. I've also included the Crystal Report as it stands so that you could look at it if you'd like. I saved it with the data so it should work.

Chart Screen Shot:

http://cmisolution.com/scott/screenshot01.gif

Formula Screen Shot:

http://cmisolution.com/scott/screenshot02.gif

Change Group Screen Shot:

http://cmisolution.com/scott/screenshot03.gif

Crystal Report:

http://cmisolution.com/scott/graph.rpt

 

[noxum]

Are you ready?
I think this will work for you....

Go into the Chart Expert...
Under the &quot;Data&quot; tab in the section where you put your On change of field (Respondent.q1 -s)
Click on the &quot;Order...&quot; button. From here it will look familiar. Under the &quot;Others&quot; tab select to Discard All Others. Click on Ok and tadaa!!!

Hope that helps.

If you have an email, I could email you your fixed report.

Noxum

 

[sbalun]

Yeeeehaa

http://wwww!!!!!

Man, oh, man. Thank you soooo much. This has definitely been an uphill battle. And I couldn't have won without your help.

Sheesh, I hope I don't struggle this much the rest of the way trying to learn this program. I don't need another ulcer....hehehe.

I also can't beleive that I missed the &quot;order&quot; button with all the times I've been inside the chart expert. I guess if you look at something long enough you start to get blinded by the obvious.

Thanks again Noxum. Sending positive vibes your way

{{{{{{{vibes}}}}}}}}}}