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

bjjao

New Member
Joined
Nov 18, 2020
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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​
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top