Plot multiple series of events on a timeline chart

**thehumble1** · 11-17-2009, 05:00 PM

Okay, I've been beating my head against this for a while and haven't gotten very far.

What I need to produce is a chart where, for each subject, there is a timeline plotting their orientation date, the date of each session they completed and if there was either a "Stepped Care" or "Use" event. Basically it seems like a couple of series within a scatter chart, but I can't even get it started because I don't know how to have the chart pick up more than one series on a plot and have it look right.

I believe it should work because I can use the Subject ID as the Y coordinate and the Date (in days from orientation) as the X coordinate for each series item, but it just isn't coming out.

Here's where it gets hairy. Each subject will only have one orientation date but could have 40 sessions over 180 days.

I know I'm not describing the process as effectively as is necessary for a quick response, but I did include a spreadsheet with the raw data at least.

Basically, for a set of subjects there are several types of events that can happen. I need to create a chart listing all of these subjects and type and day of each of these events.

Thanks for any direction you can give. I'm a bit lost right now.

**JeanRage** · 11-18-2009, 01:56 AM

Hi,

To create a timeline chart, see a nice solution ...
http://www.vertex42.com/ExcelArticle...-timeline.html

HTH

**thehumble1** · 11-18-2009, 06:13 PM

WHEW!! I don't know if anyone else was having troubles, but I couldn't connect all day.

Thanks for the tip. I found that timeline when I was doing research into this and it looks good, except that it would take about 400 hours because I'd need to manually put in WAY too much information. I have 300 subjects and from 3-40 records in each of the 5 categories for each of the 300, so it quickly becomes unmanageable unless I automate.

What I ended up doing was:

Making data sets (separate Excel sheet) out of each separate series. So, Orientation got it's own data set as did Phone sessions, office sessions and Substance Use. This way the series would only hold the information to be plotted in those series. Basically I just copied the data set and then paired it down to include only important data for each series. Orientation only contains a list of subjects that completed orientation and the number of days from a randomization point that they completed it. So when this is plotted as a series, only these points show up not rubbish from the blank spots as well.

While it isn't 100% there, it's close! Thanks!!

I can't post the final solution in it's entirety because of protocol, but