Stuck on analysing support cases (average/high/Low) pivot chart

[bjjao]

So i'm stuck on grating a pivot chart / chart for showing average / high / low trends on time from creation of a supportcase until it is closes.
What i would like to have in a chart is how average hours from creation to resolved changes over time.

I'm totally stuck on this, so any input is appreciated.

Have done some data cleaning, and have the following table. (have 86793 rows with data from 2013 -> today)

IncidentID (Formatted as general)	Created date (formatted as date)	Resolved Date (formatted as date)	Closed Date (formatted as date)	Actual resolved date (formatted as date)	CaseCloseTime (formatted as time)
1​	2.13.13 14:43​	15.02.13 10:32​	13.02.13 14:43​	15.02.13 10:32​	19:49:04​
2​	2.13.13 15:01​	14.02.13 8:53​	13.02.13 15:01​	14.02.13 8:53​	17:52:09​
3​	2.14.13 8:48​	15.02.13 10:21​	14.02.13 8:48​	15.02.13 10:21​	25:33:04​
4​	2.14.13 8:59​	14.02.13 9:25​	14.02.13 8:59​	14.02.13 9:25​	0:26:12​
5​	2.14.13 10:19​	22.10.13 4:41​	14.02.13 10:19​	22.10.13 4:41​	5994:22:06​
6​	2.14.13 10:36​	15.02.13 10:02​	14.02.13 10:36​	15.02.13 10:02​	23:26:07​
7​	2.14.13 10:36​	15.02.13 10:01​	14.02.13 10:36​	15.02.13 10:01​	23:25:10​
8​	2.14.13 10:37​	15.02.13 10:01​	14.02.13 10:37​	15.02.13 10:01​	23:23:40​
9​	2.14.13 10:38​	15.02.13 10:00​	14.02.13 10:38​	15.02.13 10:00​	23:22:43​

 

[pbornemeier]

Some dates seem to be in m.d.yy format while others are d.m.yy, that should be resolved before graphing. I would consider removing from the data any outliers that were so far from normal as row 5 because that would throw off the average. Decide which column to group by and the size of the group (month, quarter, year). then create a pivot chart using the desired date column and case close time.

This code will create a pivot table using data on the active sheet (starting in cell A1) that is formatted as in Post#1.
It uses the Created Date as the reference and grouping by Quarters & Years.

Option Explicit
Sub CreatePivotChartFromData()
    'Put your data in Range("A1") of the active sheet then run this code.

    Dim rngInput As Range
    Dim sAddress As String
    
    Set rngInput = ActiveSheet.Range("A1").CurrentRegion
    sAddress = "'" & rngInput.Parent.Name & "'!" & rngInput.Address(True, True, xlR1C1)
    
    Worksheets.Add(before:=Sheets(1)).Name = "New_PT"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        sAddress, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="New_PT!R1C1", TableName:="PT", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("New_PT").Select
    Cells(1, 1).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("New_PT!$A$1:$C$18")
'    ActiveSheet.Shapes("Chart 1").IncrementLeft 192
'    ActiveSheet.Shapes("Chart 1").IncrementTop 15
    With ActiveSheet.PivotTables(1).PivotFields( _
        "Created date (formatted as date)")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("CaseCloseTime (formatted as time)"), _
        "Sum of CaseCloseTime (formatted as time)", xlSum
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("CaseCloseTime (formatted as time)"), _
        "Sum of CaseCloseTime (formatted as time)2", xlSum
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables( _
        1).PivotFields("CaseCloseTime (formatted as time)"), _
        "Sum of CaseCloseTime (formatted as time)3", xlSum
    With ActiveSheet.PivotTables(1).PivotFields( _
        "Sum of CaseCloseTime (formatted as time)")
        .Caption = "Max of CaseCloseTime (formatted as time)"
        .Function = xlMax
    End With
    With ActiveSheet.PivotTables(1).PivotFields( _
        "Sum of CaseCloseTime (formatted as time)2")
        .Caption = "Min of CaseCloseTime (formatted as time)2"
        .Function = xlMin
    End With
    With ActiveSheet.PivotTables(1).PivotFields( _
        "Sum of CaseCloseTime (formatted as time)3")
        .Caption = "Average of CaseCloseTime (formatted as time)3"
        .Function = xlAverage
    End With
    ActiveChart.Axes(xlCategory).Select
    Range("A15").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, False, True, True)
        
    ActiveSheet.Shapes(1).Left = 10
    ActiveSheet.Shapes(1).Top = 10
    ActiveSheet.Shapes(1).Height = 500
    ActiveSheet.Shapes(1).Width = 800
End Sub