Thursday, May 19, 2011

Usage of 3D Chart To Ease Data Analysis

Ever wondered how a 3D chart can come in handy? I want to give an example where a 3D chart can turn a tedious task into a simple procedure. It can be very useful for a quick analysis of a data set with a glimpse of an eye.

I used to work in a call center where the shift managers had to schedule shifts for their team agents every couple of weeks. The main guideline was to conduct a periodical rotation of shifts between the agents. The shift mangers used to look on the summarized count of shifts per agent and get a big headache. For roughly 20 agents, most humans I know would get a headache... Well, I came up with a remedy in the form of aligning all that data in a 3D chart. There were only 3 types of shifts and I set the axis the following way:

  • X - Agent names
  • Y - Count of shifts
  • Z - Shift types
If you click to on the above image to enlarge it, you will see two inputs at the top for the selection of the Start date and the End date. These are actually drop-down menus which determine the range of dates that would be shown in the chart. The data which is feeding the chart is based on SUMPRODUCT functions which sum up the shifts per each agent for any given date range. This is also known as SUMPRODUCT with multiple conditions.

By inspecting the chart, the shift managers could immediately conclude for instance, which agents had a majority of night shifts and prioritize these agents to do other shifts in the upcoming schedule.

Eric will dispatch the file which features the 3D bar chart and the functionality behind it to the Insiders Subscribers.

4 comments:

Wilson said...

"It can be very useful for a quick analysis of a data set with a glimpse of an eye." - can't agree with this.

http://www.tips-for-excel.com/2011/06/why-you-should-never-use-3d-charts-in-excel/

some conditional formatting in an excel table would be better or a simple 2D line chart

Excel_Geek said...

Point taken. You've obviously put considerable thought into this subject. I chalk 90% of individual preference in these matters to just that: individual preference.

Anonymous said...

Great post. I do something similar, but I really like your approach. I am going to give this one a try and I will post again. Looks cool though.

http://excelconsultant.net/excel-consultant.html

Christopher

pwned555 said...

@Wilson, What are you suggesting a separate graph for each individual?

In the example given you aren't worried about whether *Sally* worked 1,2,3, or 4 hours of day shifts that week, you're just worried that she worked CONSIDERABLY less than she did at night. So the fact that you can't tell the exact height of a line in a 3D graph isn't relevant to this example.

Having to look through 20 graphs instead of 1 graph doesn't seem logical when a 3D graph gives you enough clarification for the problem at hand.

I feel like you didn't even read the example and just saw a 3D graph and started to rant and complain about how useless they are...