Creating formula that will work in a graph

[Oglesbay]

I originally created a running total of my data that shows in the report footer and works perfectly. Now my boss wants a chart (when he didn't originally) and I know that running totals don't work in charts and I'm having a heck of a time trying to figure it out without a running total. Here is a quick rundown of what I have.

My data is in an access database and are results from surveys. The way the data was given to me is that each question gets it's own line, so if there were 5 questions then I would have the persons name 5 times, the date 5 times, 5 different questions (Eval.Question) and 5 different responses. The responses are Strongly Agree, Agree, Neutral, Disagree, Strongly Disagree (Eval.Answer).

The calculation that I'm trying to create is one that will count all the Strongly Agree's and Agree's (Eval.Answer)for each seperate question and divide it by the total number of responses of that question to give me a percentage. I think I have the second part (total responses) but I'm having a hard time with the first part. The questions will always be the same so if needed I could do something like: if {eval.question}="Question 1" then....

Anyone got any ideas? Thanks.

 

[lbass]

Please explain what the chart should look like--what on change of field, what summaries, etc. What is it supposed to demonstrate?

-LB

 

[Oglesbay]

Sorry, thought I had everything explained. For the chart, it will be a bar chart where the x-axis is the questions (there are 3) and the y-axis is the date. The time frame will be from the parameter query that I set up. What I'm thinking the "on change" field should be would be the date and set it to month. The summeries would be the formula I'm trying to figure out.

 

[lbass]

Sorry, but this still isn't clear. The X-axis shows the "on change of" field. If you want to look at the same three questions every month, then you would have two "on change of" fields, and the scale for the results (counts) would be the y-axis. A chart won't take more than two "On change of fields", so you could concatenate the question and response field, e.g., Q1-Strongly Agree, Q1-Agree, etc. as the second "on change of" field, with month being the first one.
Use count of personID as the summary field.

-LB

 

[Oglesbay]

Well the chart that I had working on another report had the date as the "on change" variable and where it was asking for the "show values" I had the percentages of the three questions and it worked great. I'm trying to replicate that and if I explaned it differntly, I apologize.

 

[lbass]

I forgot about the percentages--and that you just wanted the count of responses in the two categories. Okay, so your first "on change of" field is the date->on change of month, and your second one is the question field--assuming you want to show the percentages per question per month.

Then create two formulas:

//{@PosRes}:
if {table.response} in ["Strongly Agree","Agree"] then 1

Then create a percent formula:

sum({@PosRes},{table.question})%count({table.question},{table.question})

Add the percent formula as the summary field->check "do not summarize".

-LB

 

[Oglesbay]

I created the two formula's you said but I have an issue with the second one. I am getting an error on the first part of the formula that says "There must be a group that matches this field." Here is exactly what I have (with the full real names):

sum({@Test Formula - PosResp},{HealthStream_Evals.Evaluation}),count({HealthStream_Evals.Evaluation},{HealthStream_Evals.Evaluation})

I'm wondering about the count part where you say to count({table.question},{table.question}) Is there a reason table.question is on there twice?

 

[lbass]

Why are you using the Evaluation field? Is that the question field? If so, insert a group on it in the main report--even if you need to suppress it.

You can use any recurring field(non-null) to do the count--I just used the question field.

-LB