SharePoint 2013: How to build a graph that shows the distribution of tickets by number of days to resolution

Introduction

This posting consolidates notes with regard to developing a graph that presents the number of closed service tickets distributed (or binned) by the number of days it took to resolve them.  The result is a curve something like that shown below.

The basis for creating this graph is the Excel FREQUENCY function.  It performs a very simple procedure: give it a dataset and set of values, and it will then count the number of times (or frequency) each value occurs in the dataset.  That's it.

For this posting, the service ticket listing includes the Created date, which is entered by the system, and a Resolution Date, which is entered by a workflow triggered when the ticket's Status choice field is set to Resolved.  the difference between these two dates, Created and Resolution Date, gives the time it took to resolve the ticket in days (the default unit).  From experience, a few tickets are re-opened after having been resolved and more work is performed on them and thus the Resolution Date is not accurate for every case.  However, re-opened tickets are the exception and not the rule; and for the vast majority of tickets, the ticket Resolution Date accurately records the actual date of resolution of the ticket.  Given that the analysis covers thousands of tickets, having a few tickets with less than fully accurate resolution dates is not significant.

The following procedure walks you through the steps for creating this graph from your data.

Procedure

1. Preparation
1. A service ticket list.  This will have the Created date, of course, and it must include a date column that captures the date and time on which the ticket was deemed to have been resolved - call it Resolution Date for the purposes of this posting..
2. Microsoft Excel (2010 or greater).  This is needed to conveniently and quickly perform the frequency distribution calculations.
2. Create the data view
1. Must include the Created and Resolution Date columns.
2. Filter for tickets that have been Closed.
3. Filter for start and end dates as desired - base these on the Created dates.
4. Filter out tickets missing Resolution Date information.
5. Use column counts to verify all fields are populated.
3. Export the data
1. Select the view
2. Then perform an export (to Excel of course).
3. Choose to open the spreadsheet.
4. Delete the last two columns - unnecessary.
5. Save As CVS.
6. Re-open CVS version, and then Save As Excel.  This is the version to work with from here on out.
7. Verify that needed columns are present. 

   The Created and Resolution Date columns will usually include both the date and time that the ticket was created or resolved. This is always the case for the Created column, which is entered by the system.  However, the Resolution Date column is entered by your code or workflow and may not include the time.  This posting assumes that these columns contain both date and time.

4. Implement Date Difference Column
1. Title a new column DIFFERENCE.
2. Into this column, into the first data cell in this column, enter the following function:

   =IF(YEAR(F2)-YEAR(E2)>=1,ROUND(F2-E2,0), IF(MONTH(F2)-MONTH(E2)>=1,ROUND(F2-E2,0),IF(DAY(F2)-DAY(E2)>=1, IF(F2-E2<0.5,1,ROUND(F2-E2,0)), 0)))
   
   You will need to update the cell names in this. This equation computes the date difference in days - this is the default unit when taking the difference of two dates in Excel.

3. Drag this cell contents (equation) to the bottom of the list.  This column provides the time it took, in days, to resolve the ticket.
5. Build Frequency Distribution Data
1. Add a new worksheet to the current spreadsheet.
2. On this worksheet, label the first four columns like so:
1. DATA
2. CALCULATIONS
3. BINS
4. FREQUENCY
3. Into the DATA column, past the contents of the DIFFERENCE column from the other worksheet.
4. Use the MAX function to find the maximum in the DATA column.
5. In the BIN column, starting at "0", enter content so that the column values go from "0" to MAX(DATA) - just use the drag method to quickly populate this column with incrementing values.
6. In the FREQUENCY column, select all of the cells down to the end of the BIN column.  If the BIN column goes from "0" to "250", you will have selected all of the FREQUENCY cells next to them.  These cells are currently empty.
7. Leaving these FREQUENCY cells selected, enter the following function into the Function bar (not into an individual cell): =FREQUENCY(
8. Select all of the content in the DATA column.  This is the data array that the FREQUENCY function will work on. If the data went from A2 to A4402, you would see this in the Function bar: =FREQUENCY(A2:A4402 
   
9. Enter a comma ",".  This finishes entering the data array and begins selecting the bins array.
10. Select all of the content in the BIN column.  This is the bin array that the FREQUENCY function will work on.  
    
11. Enter a closing parenthesis. You will then see selection boxes appear for the data array and bin array contents. 
    
12. Now press CTRL+SHIFT+ENTER.  This key combination enters a function as an array into the selected cells.  After pressing this key combination, you will see the FREQUENCY column populated with data.
    
13. The BIN and FREQUENCY columns represent the X and Y coordinates, respectively, of a curve that can be plotted on the same worksheet.  To instantly graph this curve, select both the BIN and FREQUENCY columns.
14. Then, on the Insert tab of the Ribbon, click Scatter and then select the Scatter with Smooth Lines option.  
    
    The graph is instantly presented. Only the first 20 or so values were selected.
    

References

• How Can I Create a Bell Curve of My Data?
• XL: How to Create a Bell Curve Chart
• How to set up a bell curve chart in Excel 2010
• Calculating Differences in Dates & Times with Excel
• How to Use the FREQUENCY Function
• FREQUENCY function

Notes

• The DIFFERENCE equation used here performs the following tasks:
1. It first checks the start and end years.  If the end year is greater or equal to the start year, simply compute the difference between the two date values, round it, and return.
2. If they are the same, check the start and end date months. If the end date month is greater than the start date month,  simply compute the difference between the two date values, round it, and return.
3. If they are the same, check the start and end date days.  If the end day is greater than the start day, check the difference: if it is less than 0.5 (half a day) then still consider it to be a full day and enter "1".  Otherwise, simply compute the difference between the two date values, round it, and return.
1. The reason for checking this is that a ticket created late one day, and then resolved early the next, might show a difference of less than half day and then erroneously be calculated to have been completed on the same day.  To avoid this,