"Top Box" (percent "strongly agree") formula field 2

[MargaretL]

I work with patient satisfaction survey data, and am trying to create a formula that would display the "Top Box" or percent of survey responses that were "strongly agree" (for example, for each doctor or each clinic overall).

So, the raw data basically includes a row for each patient, and includes their doctor, their clinic, and their responses to each question on the survey. The responses are from Strongly Disagree (1) to Strongly Agree (5), and we want to calculate the percent of responses that were "5" out of the total responses.

In SAS, I have to run a query that counts all the responses, then another query that counts all the "5"s, and then calculate a new column/variable that divides the count of 5s by the total count, and that's my Top Box. I don't know how to do this in Crystal, though, because I am very new and do not know much about Crystal syntax.

Any help would be MUCH appreciated!

Thank you!

 

[lbass]

Try formulas like this:

//{@dr5}:
if {table.doctor} = 5 then 1

Then use a formula like this in the report footer (assuming you want this at the report level):

sum({@dr5})%count({table.patient})

-LB

 

[AnnetteB1305]

Hello,

Are you wanting to show the raw data in the report as well?

You could insert a group and group by table.response and then insert a sum and summarise this field as a count and insert this at group level and at report level. This will then summarise count your responses and give you your total responses. You could then add another summary the same but tick the option show as a percentage of and it will already have the grandtotal selected. This will give you each response as a percentage of your total responses. If I've understood you right. You could also add a group for doctor and clinic ( have clinic as group 1, doctor as group 2 and responses as group3)

The other way is to do formulas as you have in SAS

create a formula

//@allresponses
count({table.response})

create a formula
@null
where you save this with nothing in the formula

create another formula
//@5responses
If {table.response}='5' then count ({table.response}) else tonumber({@null})

create the final formula
//{@topbox }
{@5responses}/{allresponses}


Thanks,

Annette

 

[MargaretL]

Hi Annette,

Thanks for your response. To your question, I do not want to show all the raw data in the report.

In the report, I will be grouping by physician, and showing the question text, and then the "Top Box" (% strongly agree) for each question, as well as the number of total responses and a mean score.

So, I will try to use your formulas and see what happens.

Thanks!

 

[lbass]

Just to clarify. Annette's second solution will not work correctly, although the first one will.

If you add a condition like:

if {table.field} = "X" then count({table.field}) else <anything>

... this just says, if the field = "X", show the count for ALL values--it will NOT show the count of the field WHEN it is equal to "X". To do that, you have to use the approach I showed above.

-LB