2

My Data looks like this:

||UNIQUE_KEY||LABEL1||LABEL2||START_DATE||END_DATE||

What I'd like to do is have 2 stacked area charts that show totals (count of UNIQUE_KEY), one chart for LABEL1 values & another for LABEL2values over time.

X axis would be Number, Y would be time. Charts would show a count of UNIQUE_KEY for each of the possible LABEL# values at each point in time.

I am at a loss how to translate the Start Date and End Dates into a framework so that excel considers that Issue open at that point in time for the dates that fall in between Start and End date.

I can think of ways to do it that are really ugly and I have to know id there's a slick way to get what I want.

Thanks!

edit... here's a snippet of the data (comma delimited) - and I refined my explanation above

Issue key,Issue Type,Work Type,Created,Resolved
SA-17070,Maintenance,PS_SYSTEM,7/7/2014,8/29/2014
SA-17006,Production Incident,PACKAGING,6/30/2014,8/29/2014
SA-23110,Production Incident,BUSINESS_PROCESS,8/1/2016,9/12/2016
SA-22628,Enhancement,BUSINESS_PROCESS,5/23/2016,8/25/2016
SA-16073,Maintenance,BUNDLE,4/17/2014,5/16/2014
SA-15625,Maintenance,BUNDLE,3/6/2014,3/14/2014
SA-17008,Maintenance,BUNDLE,6/30/2014,7/3/2014
SA-17062,Maintenance,BUNDLE,7/3/2014,7/7/2014
SA-16922,Maintenance,BUNDLE,6/19/2014,7/7/2014
SA-16913,Maintenance,BUNDLE,6/18/2014,7/7/2014
SA-17064,Maintenance,BUNDLE,7/7/2014,7/8/2014
SA-16956,Maintenance,BUNDLE,6/24/2014,7/28/2014
SA-17172,Maintenance,BUNDLE,7/15/2014,8/29/2014
SA-17349,Production Incident,BUNDLE,8/4/2014,8/7/2014
SA-19038,Configuration,BUNDLE,2/16/2015,2/19/2015
SA-19011,Maintenance,BUNDLE,2/12/2015,2/16/2015
SA-19298,Maintenance,BUNDLE,3/12/2015,3/23/2015
SA-19065,Production Incident,BUNDLE,2/18/2015,3/23/2015
SA-19235,Production Incident,BUNDLE,3/9/2015,4/30/2015
SA-20192,Maintenance,BUNDLE,7/13/2015,7/15/2015
SA-21317,Maintenance,BUNDLE,11/19/2015,12/2/2015
SA-21169,Maintenance,BUNDLE,11/3/2015,2/1/2016
SA-21283,Production Incident,BUNDLE,11/17/2015,12/11/2015
SA-21329,Production Incident,BUNDLE,11/19/2015,1/4/2016
SA-23487,Maintenance,BUNDLE,9/12/2016,10/25/2016
SA-23356,Maintenance,BUNDLE,8/29/2016,10/25/2016
SA-23443,Production Incident,BUNDLE,9/7/2016,9/9/2016
SA-23589,Production Incident,BUNDLE,9/24/2016,9/24/2016
SA-23931,Enhancement,BUNDLE,11/21/2016,12/2/2016
SA-17356,Configuration,PS_SYSTEM,8/4/2014,10/7/2014
bhnn
  • 225
  • 5
  • 17
US_Bennett
  • 31
  • 2
  • 1
    We need a more clear understanding of you question. If it's this: how to translate the Start Date and End Dates into a "Issue was open for this long..." type view; I would make another column using `END_DATE - START_DATE` and put a conditional format on that `DURATION`. If you really want a chart, please provide an example of the data. – UndeadBob Feb 08 '17 at 19:29
  • Done, thank you! And I updated my explanation because after I spent the night thinking over it I came to a clearer understanding of what it is I need exactly. – US_Bennett Feb 09 '17 at 15:21
  • I'm not in front of a computer for another hour, but I have some ideas. It would be good to know if your interested in "what issues were open in this day/week/month" or "which dates had the most issues" – UndeadBob Feb 09 '17 at 17:18

1 Answers1

1

This is not exactly what you described. This shows dates an issue is open on the Y-Axis, versus the issue number on the X-Axis. I know you want something different on the X-Axis, but it is unclear how you want this to map to the Y-Axis. (e.g. maximum Resolved time - minimum Created time?, duration of latest issue?)

Note: You could do the following once for each Issue Type, once for each Work Type.

To create a chart showing duration an issue is open, where duration is on the Y-axis and the issue "key" is on the X-axis ...

enter image description here

  1. Add a column to your data that is Duration. It is Resolved - Created.
  2. Select the Issue Key Column, Created Column, and the Duration column.
  3. Insert a Stacked Column Chart.
  4. Select the Resolved series and format. Set the fill color and line color to None.
  5. Select the Y-axis and format the axis. Adjust the lower limit to something you want. Change the number format to Date.
OldUgly
  • 2,129
  • 3
  • 13
  • 21
  • I didn't have time this afternoon after all. But I'm glad OP was able to fix the problem statement to get a decent answer. I like it and I learned something. Thanks. – UndeadBob Feb 10 '17 at 03:46