Chart Similar to Gantt, but Collapsed

**allenrg** · 09-02-2009, 04:31 PM

I am having difficulty generating a chart that shows detailed run times of our application batch processes. It is similar to a gantt, but I need the lines collapsed showing all the runs for a specific application even if it overlaps. As this is very hard to explain I have attached a generic looking graphic representation of how we would like this to look (this doesn't match up to the data in any way). I have also attached a screen print of the spreadsheet. So, the data in spreadsheet is the App Name, Start Date & Time and Duration.

I have tried to do the stack bar chart and remove the outline and fill for the main part of the bar, but that leaves all of these on seperate lines. I need entries with the same app name on the same line, if that helps.

I figured that you guys have a fairly simple work around for this.

Thanks,
Rob

**teylyn** · 09-02-2009, 06:20 PM

Hi,

this is way more complicated than a simple workaround. This post is not a solution, just a few ponderings.

You will need a pretty sophisticated data layout for this, if it can be done at all.

Using stacked bar charts, you need to place all your values for the same app in the same column, i.e you need a table with a column for each app and rows for your values.
This is where it gets complicated.

Issue 1
You want to have an x axis with dates on it and you want to plot events that take only seconds. This won't fly unless you have a really wiiiiiiide chart, otherwise, you won't be able to see your data points

Issue 2
within each day, your events start at different times and have different durations. You need to add data points (i.e. rows) for the gaps between the events and either calculate or manually enter the duration of the gaps.

Issue 3
You can then plot the data and end up with a whole lot of series. If you've placed your data right, every other series will be the gap between events, which you can format with no border and no background to make invisible. The remaining series are your events, but you will need to manually colour every individual data point to assign a consistent color to an app.
the attached file shows a SIMPLE chart with 8 data series.

Issue 4
You'll probably soon hit the limit of data series per chart with this layout. I don't know how many Excel will take.

Sorry, but this is really bigger than you expected, right?

**Andy Pope** · 09-03-2009, 04:30 AM

As teylyn points out this is not a simple problem to work around.

You could use a XY-scatter chart to plot each apps data. You will need to construct the xy data points from you source data.