| Author |
Message |
bryonwoods
Newbie
Joined: 15 Feb 2011
Online Status: Offline
Posts: 16
|
  
Topic: somewhat complex chart
Posted: 15 Feb 2011 at 8:35am |
Hi,
I need to create a horizontal stacked/percentage bar chart.
I have one column of data that has the numbers 1-5 as results.
1 and 2 need to be group together as a percent, 3 by itself, 4 and 5 grouped together.
So the results chart would be a single bar that shows a percent for the values 1,2 combined, percent for 3 on its own, and percent for 4,5 combined.
Would love some bullet point steps on how to create this.
Thanks =)
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Feb 2011 at 8:47am |
create a formula to convert your data into your new "buckets" and then use that instead of the original field for your bar chart
if table.field in [1,2] then "1 & 2" else if table.field = 3 then "3" else if table.field in [4,5] then "4 & 5" else 'Missing'
|
| IP Logged |
|
bryonwoods
Newbie
Joined: 15 Feb 2011
Online Status: Offline
Posts: 16
|
Posted: 16 Feb 2011 at 5:01am |
Thanks. I was able to create the formula field and it grouped the properly. However when great a new chart/graph I need for them to be a horizontal stacked bar, but for some reason no type of graph i choose in the chart expert it keeps giving me separate bars for each "bucket".
I need a single bar that shows 3 segement percents that total up to 100%.
For example the 1,2 grouped values have a bar that takes up 40% of the total bar, value 3 takes up 20% and grouped values 4,5 take up the rest.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Feb 2011 at 5:05am |
|
please show some sample row level raw data
|
| IP Logged |
|
bryonwoods
Newbie
Joined: 15 Feb 2011
Online Status: Offline
Posts: 16
|
Posted: 16 Feb 2011 at 5:28am |
As shown here, it is an access database, raw data is in columns. i will rsq01 is question 1 and so on. I will need to create individual stacked horizontal percent charts for each.
I have also included a picture of the graph we are trying to get to.
3 sections that are made of up the 1,2 group, value 3 and the 4,5 group
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Feb 2011 at 6:21am |
columns per question are brutal for this and not sure how you wourl do it.
are you able to do union statement on each seperate column?
select unique id, RSQ01 as answer, 'Q1' as question
from table
union
select unique id, RSQ02 as answer, 'Q2' as question
from table
union
...
from there it is pretty straight forward using the chart expert...
|
| IP Logged |
|
bryonwoods
Newbie
Joined: 15 Feb 2011
Online Status: Offline
Posts: 16
|
Posted: 16 Feb 2011 at 6:48am |
Oh I am not worried doing it per question. I am creating 1 graph per question, so the graph only needs to read from that one column. I created the formula field you suggestion for rsq01. now i just need to know how to create the graph in the data tab of the chart wizard to get it to actually just give me one chart. All types of charts i have chosen keep giving me separate bars for each 'group' instead of one stack bar.
in the chart expert i am putting my calculated field under the 'on change of', then i am not quite sure what to do with the show value's box as everything i put in there hasn't came out with a proper graph.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Feb 2011 at 6:55am |
Make another formula field called Question1
as
'Question1'
add a chart in RH or RF
get into the design
on type tab
choose BAR > "percent bar chart > horizontal
on data tab
on change of first add @question1 then add @bucket1
on show values add a primary key fieldand chaneg it to a distinctcount Edited by DBlank - 16 Feb 2011 at 6:55am
|
| IP Logged |
|
bryonwoods
Newbie
Joined: 15 Feb 2011
Online Status: Offline
Posts: 16
|
Posted: 16 Feb 2011 at 7:07am |
Just need a clarification.
The first formula field I created I called "Question 1" and use the formula
if {ResponseTable.RSQ01} in [1,2] then "1 & 2" else if {ResponseTable.RSQ01} = 3
then "3" else if {ResponseTable.RSQ01} in [4,5] then "4 & 5" else "Missing"
You are saying I need to create a second formula field with the same formula?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Feb 2011 at 7:13am |
nope, sorry for the confusion.
need to different formulas one retruens the string 'Question1' all the time which is your first level of chart grouping the second returns your 'buckets' for counting as you wanted.
you can rename the ofmruals but I am using the below to help clarify...
formula1 named 'Q1 text' as:
'Question 1'
formula2 named 'Q1 answers' as:
if {ResponseTable.RSQ01} in [1,2] then "1 & 2" else if {ResponseTable.RSQ01} = 3
then "3" else if {ResponseTable.RSQ01} in [4,5] then "4 & 5" else "Missing"
in your chart for on change of use
@Q1 text first then @Q1 answers next
|
| IP Logged |
|
|
|
