How to draw out a bar chart backed by a 3-dimension database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone:

I've a problem: I'm drawing a population pyramid bolstered by a
3-dimensional database, with the population as the only figure to display,
and break up by gender, age group, and record year; e.g.

Year Gender Agegroup Population
2000 Male 10-14 200000
1999 Female 5-9 1340000

I've got a total 2 years in year break up, 2 gender types, male & female,
and about 10 age groups;

What I wanna to do is to generate a bar chart, with age break displayed on
the vertical axis, population displayed on the horizontal axis, and break up
by gender, shown on alternative quadrants of horizontal axis devided by
vertical null point (treat female figure as negative), and stack the year
data on the vertical axis, show an example:

++++++|++++++++ Year 2000,Age group 15-19
*****|****** Year 2005,Age group
15-19
+++++++++|++++++++++ Year 2000,Age group 10-14
*******|******* Year 2005,Age group 10-14
++++++|+++++++ Year 2000,Age group 5-9
*******|********* Year 2005,Age group 5-9
Female _____________|_______________Male

Where * and + represents different years' break, left and right quadrants
represent gender break, and vertical axis represent age break, the length of
+ and * represent the population figure;

I've been doing this graph using a statistical software and I would like to
move it into excel file to link the datapoints to other files, enabling this
chart manipulatable by altering the figures to obtain dynamic effects, how
can this be achieved?

Thanks & appreciates!
 
Bernard has pointed you in the direction of creating a tornado chart by hand.
But, it doesn't quite address your requirement.

What you need to do is the following:

Extract the data to Excel (use either a PivotTable (Data | PivotTable or
PivotChart Report...) or MS Query (Data | Import External Data > New data
query...). Now, add a new column to this extracted data with a formula to
convert the population to -ve if the gender is F.

[This intermediate step would not be necessary if one could use a PT
Calculated Field that with a formula like =if(gender="f",-popn, popn) but I
cannot get a Calculated Field with that formula to work.]

Now, use this table as the source for a PivotChart. The age group is the
first row field, the year the 2nd. The Gender is the only column field, and
the new column is the data field.

Convert the chart to a Bar chart, and format the series to have a 100%
overlap and adjust the gap width to taste.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
 
Thanks so much!

Got your idea!

Tushar Mehta said:
Bernard has pointed you in the direction of creating a tornado chart by hand.
But, it doesn't quite address your requirement.

What you need to do is the following:

Extract the data to Excel (use either a PivotTable (Data | PivotTable or
PivotChart Report...) or MS Query (Data | Import External Data > New data
query...). Now, add a new column to this extracted data with a formula to
convert the population to -ve if the gender is F.

[This intermediate step would not be necessary if one could use a PT
Calculated Field that with a formula like =if(gender="f",-popn, popn) but I
cannot get a Calculated Field with that formula to work.]

Now, use this table as the source for a PivotChart. The age group is the
first row field, the year the 2nd. The Gender is the only column field, and
the new column is the data field.

Convert the chart to a Bar chart, and format the series to have a 100%
overlap and adjust the gap width to taste.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


Aaron said:
Hello everyone:

I've a problem: I'm drawing a population pyramid bolstered by a
3-dimensional database, with the population as the only figure to display,
and break up by gender, age group, and record year; e.g.

Year Gender Agegroup Population
2000 Male 10-14 200000
1999 Female 5-9 1340000

I've got a total 2 years in year break up, 2 gender types, male & female,
and about 10 age groups;

What I wanna to do is to generate a bar chart, with age break displayed on
the vertical axis, population displayed on the horizontal axis, and break up
by gender, shown on alternative quadrants of horizontal axis devided by
vertical null point (treat female figure as negative), and stack the year
data on the vertical axis, show an example:

++++++|++++++++ Year 2000,Age group 15-19
*****|****** Year 2005,Age group
15-19
+++++++++|++++++++++ Year 2000,Age group 10-14
*******|******* Year 2005,Age group 10-14
++++++|+++++++ Year 2000,Age group 5-9
*******|********* Year 2005,Age group 5-9
Female _____________|_______________Male

Where * and + represents different years' break, left and right quadrants
represent gender break, and vertical axis represent age break, the length of
+ and * represent the population figure;

I've been doing this graph using a statistical software and I would like to
move it into excel file to link the datapoints to other files, enabling this
chart manipulatable by altering the figures to obtain dynamic effects, how
can this be achieved?

Thanks & appreciates!
Click to expand...
Click to expand...
 
Back
Top