Creating a complex graph

**chiggy99** · 05-08-2009, 07:31 AM

Hi everyne,

This is my first time using a forum, so please let me know if i am doing anything wrong.

Attached is an excel file with some data.

There are 3 columns:

Area: The area where the work is taking place

Start Date: Start Date

End Date: End Date

what i am trying to do, is create a graph which has calendar dates across thh x axis (preferreably incrementing by weeks), and on the y axis i want a count.

The count should represent the number of active "works" taking place at any given time(Filtered by the area of work)

What i want is a drop down menu giving the option of selecting any of the 25 areas of work....

when i select an area of work eg P1, i want to see the number of active works displayed against the dates.

Hopefully this makes sense and someone can help me. Please let me know if you require any extra information.

**Andy Pope** · 05-08-2009, 07:47 AM

How about the attached pivot table and chart.

You don't get dates across the x axis. Instead you get a category for each unique start/end date.

**chiggy99** · 05-08-2009, 09:07 AM

Thanks for teh quick reply, but i was really looking to get the dates acroos the x-acis....is there any way to do that?

**Andy Pope** · 05-08-2009, 09:11 AM

the problem is that you don't have data for the dates in between start and finish.
You would have to generate counts for all the dates in between.

Or you would just have a count on the start date and end date.

But which ever way you go you will need to mess around with the layout of the data in order to get a chart

**chiggy99** · 05-08-2009, 09:25 AM

So is it not going to be possible to produce the graph that i want?

if the start date is 01/01/09 and the end date is 01/02/09 i want there to be a count of 1 for each week in between those dates......which indicates that there is one piece of work taking place between those dates....obviously the dates for different works will over lap and show a peak and trough in the amount of work that needs to be done?

Is that what you have understood from my original question?

Thanks a lot for your help on this.

Regards.

**Andy Pope** · 05-08-2009, 09:35 AM

You can produce the chart you want it's just you will also have to produce the data to go with it.

If you have

Start: 1/Jan/2009
End: 1/Feb/2009

that will give you a chart with 2 columns of 1 at either end of the date span of the x axis.
So in order to get a column for all the intervening dates you will need to generate data.

Start: 1/Jan/2009
Working: 2/Jan/2009
Working: 3/Jan/2009
Working: 4/Jan/2009
....
Working: 30/Jan/2009
Working: 31/Jan/2009
End: 1/Feb/2009

**chiggy99** · 05-08-2009, 10:30 AM

ok, i understand now.....

going to be a bit difficult to do that for all 750 row entries.....

do you know of any quick way to generate the data based on the information i have?