• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combination and Multi Layer Chart

T

Travis

New Member
I am trying to identify a totals on activity over the past few years, by week, using a bar chart, followed by breaking that data out into compnants with the same axis but using a line chart.


X = Each week for the past three years

Y = The amount of activity


Bar = Total Activity (in the background)

Line = 5 componants of the total (in front of bar)


Thank You
 
Bar= Total activity per week

Line= Total activity per week by type


One location at a time.


I assume I would first filter out the location I did not want, then I can create a pivot chart, or bar graph based on all the activity, but im lost after that, getting the line and the bar graphs on the same chart.


Date Type Location

1/1/2012 1 A

1/2/2012 2 B

1/3/2012 1 A

1/4/2012 3 B

1/5/2012 3 B

1/6/2012 2 B

1/7/2012 2 A

1/8/2012 4 A

1/9/2012 4 B

1/10/2012 3 B

1/11/2012 5 A

1/12/2012 5 A

1/13/2012 1 A

1/14/2012 2 B

1/15/2012 1 A

1/16/2012 3 B

1/17/2012 3 B

1/18/2012 2 B

1/19/2012 2 A

1/20/2012 4 A

1/21/2012 4 B

1/22/2012 3 B

1/23/2012 5 A

1/24/2012 5 A

1/25/2012 1 A

1/26/2012 2 B

1/27/2012 1 A

1/28/2012 3 B

1/29/2012 3 B

1/30/2012 2 B

1/31/2012 2 A
 
Travis


Firstly, Welcome to the Chandoo.org forums


I assume your totals are the Count of Total A & B each month and the 5 components are Types 1 - 5


Why not just do a Stacked Column or Stacked Area Chart

The Height of the Total is the total and Each component will be shown each month as appropriate
 
Thanks Hui,

I look forward to gaining a better understanding on excel


I should have been more clear.


(Bar Graph) I need to represent the days (Date) into weeks, 7 day periods, for location A, then do this again for location B. Each seven day period will have as the Y axis the amount of "Types" for that week.


One the same graph I would like to overlay a Line Graph, each "Type" by week. So, 5 different lines each representing Type 1-5.


Am I making sense.
 
Travis


Have a look at what I think you are after

https://www.dropbox.com/s/h6ceh3d4o8qmpv7/Travis1302.xlsx


I have put 4 alternative charts,

I'm sure one will be what you are after
 
Hui,


They are all right on the money, Thank You, I will use the bottom left chart for my analysis. Will this work with a large data set?


would you be able to explain the process?


Travis
 
Each location will be represented individually, If you dont mind explaining the methodology with the data below, which should be more realistic, and have a better looking line chart.


Thank You


Type Date Location

2 14 Mar 12 A

3 12 Mar 12 A

4 11 Mar 12 A

4 11 Mar 12 A

4 11 Mar 12 A

4 11 Mar 12 A

3 10 Mar 12 A

3 10 Mar 12 A

3 10 Mar 12 A

3 10 Mar 12 A

3 10 Mar 12 A

3 10 Mar 12 A

4 8 Mar 12 A

3 7 Mar 12 A

4 7 Mar 12 A

4 6 Mar 12 A

1 5 Mar 12 A

1 5 Mar 12 A

1 27 Feb 12 A

4 24 Feb 12 A

2 22 Feb 12 A

1 20 Feb 12 A

1 13 Feb 12 A

1 13 Feb 12 A

1 13 Feb 12 A

1 13 Feb 12 A

1 13 Feb 12 A

1 13 Feb 12 A

2 11 Feb 12 A

5 5 Feb 12 A

4 27 Jan 12 A

5 26 Jan 12 A

3 20 Jan 12 A

1 16 Jan 12 A

2 14 Jan 12 A

5 10 Jan 12 A

5 9 Jan 12 A

5 9 Jan 12 A

5 9 Jan 12 A

5 9 Jan 12 A

5 9 Jan 12 A

5 8 Jan 12 A

5 8 Jan 12 A

3 4 Jan 12 A

2 1 Jan 12 A

2 1 Jan 12 A

2 1 Jan 12 A

2 1 Jan 12 A
 
Travis

https://www.dropbox.com/s/3ot2xqxrityawkh/Travis1302b.xlsx


Area 1. Data area

Area 2. Data Extra area

Extract data based on formulas to sumarise the data weekly and by Type/Location

Are 3. Chart

A Chart of the Data Extract area First two series are on the left axis and are columns, the last 5 series on the Right Axis and are line charts
 
You must log in or register to reply here.
Back
Top