Chart showing group averages as well as Total.

[zigstick]

Hello, using CR XI and have created a bar chart on change of 2 fields, Year and Rating ... showing the average rating by year.

Ex:
Group 1: Year
Group 2: RatingValue 5 - Excellent (avg for all values)
3 - Good
1 - Poor


This is easy enough to graph obviously, but I need to show a an overall average by year below the others on my bar chart.

ex: Rating 5 - bar
Rating 3 - bar
Rating 1 - bar
Total of all 3 - bar (this one is the killer)

Seems like it should be simple, like me. Any suggestions much appreciated. Apologies in advance if I haven't explained this very well. Thank you!

 

[lbass]

Please explain exactly what you have placed in the chart expert for "on change of" fields and for "show value" fields, using actual field names, and identify the type of summary used in the show value area.

-LB

 

[zigstick]

On Change of
vAllYearsCombined.Question-A
vAllYearsCombined.SurveyYear-A

Show Value(s)
Avg of vAllYearsCombined.ResponseValue

This gives me a bar for each year, by question.

I've been asked to show another set of bars by year for the average of ALL questions. Thanks LBASS!

 

[lbass]

You could do this in a command set up like this:

SELECT
(
select avg(A.`ResponseValue`)
from vAllYearsCombined A
where A.`SurveyYear-A` = vAllYearsCombined.`SurveyYear-A`
) as Amt, 'All' as Question, vAllYearsCombined.`SurveyYear-A`
FROM vAllYearsCombined

Union

SELECT
(
select avg(A.`ResponseValue`)
from vAllYearsCombined A
where A.`SurveyYear-A` = vAllYearsCombined.`SurveyYear-A` and
A.`Question-A` = vAllYearsCombined.`Question-A`
),
' '+`vAllYearsCombined.Question-A`, vAllYearsCombined.`SurveyYear-A`
FROM vAllYearsCombined

Not sure what your database is, so you may need to change the punctuation and syntax. I added a space before the Question field so that it would sort above the "All" in your horizontal bar chart.

You would add {command.Question} and {command.surveyyear-A} as on change of fields, and then add avg of {command.amt} as your "show value" field.

This worked when I tested it.

-LB