+ Reply to Thread
Results 1 to 4 of 4

Plot 2 sets of duration data on the same chart

  1. #1
    Registered User
    Join Date
    05-25-2020
    Location
    California, USA
    MS-Off Ver
    16.37 Mac, Office 365 ProPlus
    Posts
    2

    Plot 2 sets of duration data on the same chart

    Hello brilliant people...

    I work in customer support and I have two sets of data...
    1. Duration of time spent at each stage
    2. Duration of time spent at each status

    Stages mostly progress in a straight line (Initial Investigation --> Collaboration --> Engineering Escalation --> Bug Fixing --> Solution Provided) for example.
    Statuses jump back and forth (New, Customer Pending, Customer Updated, Vendor Pending, Customer Pending, Customer Updated, Vendor Pending, Closed) for example.

    The total duration of stage and the total duration of status add up to the same value for a support case.

    Thinking about our support processes from beginning to end, plotting these two sets of data would help us understand where we need to focus on our next support process improvement initiative.
    What I thought might help visualize the data is either...
    • Two stacked horizontal bar charts on the same graph. One for stage and one for status.
    • A single horizontal stacked bar chart (for stage) with scatter plots with error bars or skinning vertical bars that would appear like tick marks on a timeline. Or vice versa (status is stacked while stage is scatter/bar).

    I've tried the first option, but I cannot get the second dataset to plot it's own horizontal stacked bar. It's always plots it on the first one. I've tried the second option, but I'm lost and think the first option chart would look nicer.

    Data basically looks like this...
    Dataset 1
    Stage / Duration
    Investigation / 30
    Collaboration / 20
    Eng. Escalation / 200
    Solution Provided / 100

    Dataset 2
    Status / Duration
    New / 5
    Vendor Pending /10
    Customer Pending / 20
    Customer Updated / 10
    Vendor Pending / 155
    Customer Pending /10
    Vendor Pending / 45
    Customer Pending / 45

    Because Status values can repeat, and stage values once in a while, each value should be the same color. That is, if the status was Customer Pending 6 times, it should be represented by a single color, not 6 colors.
    It would be great if the chart can come from a pivot table so it can update based on various slicer settings. Then we could see average of all cases, average per customer, average per process, average per year, etc.

    This would help answer questions like, why does the Investigation stage take x time? Ah, because we are waiting on customer of 80% of that time. Let's come up with a better process to get customer response.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by jdogcisco; 05-25-2020 at 09:56 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,315

    Re: Plot 2 sets of duration data on the same chart

    It's difficult to be specific with the description you've given. A sample file might be easier for us to give specific suggestions.

    Your option 1 seems to me to be a "clustered and stacked" bar chart. It's not difficult to make such a chart in Excel. Most of the "work" is arranging the data in the spreadsheet, and then the chart is a normal stacked bar chart. See if this tutorial helps: https://peltiertech.com/clustered-st...mn-bar-charts/ (this is the usual tutorial I link to, though Peltier's site seems to be having trouble for me right now. If you have trouble, try his site again later. If it still doesn't come up, then we'll have to find/make another tutorial).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-25-2020
    Location
    California, USA
    MS-Off Ver
    16.37 Mac, Office 365 ProPlus
    Posts
    2

    Re: Plot 2 sets of duration data on the same chart

    Hello MrShorty,

    Thanks for the reply. Unfortunately the site doesn't seem to be up (google cache didn't have anything either). I did upload an attachment moments after creating the link. I hope you saw that. The 'stage' seems to draw correctly, but it's labeled 'duration'. The status values aren't stacking. If I try to get them to stack, they stack on the same stacked bar with the stage data.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,315

    Re: Plot 2 sets of duration data on the same chart

    I must have missed your sample file.

    It looks like Peltier's site is back up, so have a look at his tutorial. I think if you arrange your data like he does then build a stacked bar chart, you will get what you want. As I said, most of the work for this clustered and stacked chart is arranging the data in the spreadsheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-28-2014, 05:05 PM
  2. [SOLVED] plotting 2 sets of data on the same scatter plot using 2 symbols
    By Wombat18 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-07-2013, 04:04 AM
  3. Plot two sets of Data into one chart
    By e@london in forum Excel General
    Replies: 1
    Last Post: 08-21-2012, 09:04 AM
  4. How do I plot multiple data sets on a whisker chart?
    By Zahra in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-19-2012, 11:11 AM
  5. Replies: 0
    Last Post: 12-21-2011, 12:52 PM
  6. How do you plot 2 sets of data on the same scatter graph?
    By hunterage2000 in forum Excel General
    Replies: 2
    Last Post: 03-17-2010, 08:25 AM
  7. Plot 3 sets of data in scatter chart and area chart in one
    By geoffs52 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-24-2009, 04:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1