This is More of a Display Question...

[skinnea]

I latched onto a recent thread (http://www.mrexcel.com/forum/showthread.php?p=2788145#post2788145) thinking it might help me display some info I've got in my business. But other that highlighting how bad I was at following simple instructions I'm not quite sure it gave me what I was after.

Let me explain.

I've got 7 business areas called Domains. They each do 'things' across various days each week (like release new software, install new hardware, fix code bugs, upgrade components, and such). Each of the things they do has a risk/impact rating (Low, Medium, High for each). Each of these things also takes a different amount of time to complete, from 30 minutes to 6 hours.

I want to represent the spread of these things in some way that highlights clusters of activity. That's because those clusters might represent increased business threat (if things go wrong, or the activities interact in some unforeseen way); in the same way, a lack of clusters might show up good times to do other activity, or spare resource capacity.


I'd imagined time running across the X axis and domains running up the Y axis, the duration of each activity being either a line or a bubble, and the colour of each activity corresponding to its risk/impact rating (green being Low/Low and Red being High/High, and others in-between).
But I could be swayed from this if there's a better way of doing it.

Last thing; this has to be simple to update weekly, or even daily; we deal with over 30,000 of these 'things' a year, so if it takes ages to update every time it'll not get used.


Any ideas at all...?

 

[Gerald Higgins]

Hello.

If you really want a bubble chart, like in that other thread, I'd keep going in that other thread. You've had posts from some real experts there.

HOWEVER, if I were you, I'm not sure I'd want a bubble chart - I think I'd prefer to use some kind of colour coded gantt chart, which shows bars for each task, with the length of the bar driven by task duration, and colour coded based on risk / impact.

This is reasonably straightforward to do, and maybe I can talk you through it.

 

[skinnea]

If you can spare the time to talk that through, that'd be brilliant...

 

[Gerald Higgins]

OK.

Alot of this will depend on how you set your data up, and also on having a clear idea of what you want to display.

I'm assuming we're going for a chart with horizontal bars, one for each task, where the length of the bar is driven by the duration of the task, and the position of the bar is driven by the start point of the task.
Have I got this right ?

You then need to consider how you are going to represent time on the chart.

There are probably three main options -
1) Day by day, single day is the smallest unit
2) Day and time, 24 hours per day, showing details within each day
3) Day and time, less than 24 hours per day, for example an 8 hour shift.
Which of these do you want, or do you want something else ?

For each task, you will need the following pieces of data
1) Start date/time
2) Duration (or end date/time, from which we can derive duration)
3) Risk factor, or whatever it is that drives the colour coding.
In your OP, you mentioned Risk/Impact - is this two separate variables that come together somehow ? If so, how does that work exactly ?

Also, which version of Excel are you using ?

 

[shg]

It seems that if there were 30000 events per year ranging in duration from 30 min to 6 hours, there would be many occurring simultaneously, which would be messy for a Gantt chart.

Assuming the division across Domains is fundamental, then you could have 21 small charts (7 domains, 3 risk levels) arrayed in a table, and each chart would plot the number of simultaneous activities for a given domain and risk level.

There's an example with some dummy data (F9 to recalculate) at http://www.box.net/shared/i6gy9gb9o2ejzjp9sb2h

There are some UDFs in the file to help generate the dummy data; they would not be necessary in a final implementation.

Or you could use a stacked column chart for each domain, with three segments representing the number of simultaneous activities at each risk level.

 

[Gerald Higgins]

Sorry I missed the point about "30,000" in the OP.
shg is right - 30,000 tasks on a gantt chart is totally unmanageable.
If you want to do some kind of gantt chart, let me know and I'll try and help you.

 

[skinnea]

it's 30,000 per year - and I'd only want to show a week or a month ahead at most.

Let me get back to you with some data examples shortly...Gerald Higgins you're on the right track.