SLA Performance advice needed

[donnalouiseb74]

I'm trying to measure SLA performance, and as automated as possible so idealy the maths caluclation would be SLA compliant tickets/Total Calls. So in my data model I have a field ticket [ticket_withinclosesla] the date field. I'm just struggling with how to write the expression. Ideally I want it to look at the month year, do the SLA calculation and return me a single percentage.

Any one done something similar?

 

[sijpie]

You want say the percentage of calls closed within the agreed period compared to total number of calls?

Something like this should do the trick:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Ticket Nr	Date started	Date Closed	Duration	Agreed	Over	Month	Counts Over	Total Tickets	% On Time

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01-Aug[/TD]
[TD="align: right"]06-Aug[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01-Aug[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]05-Aug[/TD]
[TD="align: right"]21-Aug[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01-Sep[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12-Aug[/TD]
[TD="align: right"]18-Aug[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01-Oct[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]03-Sep[/TD]
[TD="align: right"]05-Sep[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01-Nov[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]04-Sep[/TD]
[TD="align: right"]21-Sep[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]07-Oct[/TD]
[TD="align: right"]24-Oct[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]08-Oct[/TD]
[TD="align: right"]10-Oct[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]14-Oct[/TD]
[TD="align: right"]18-Oct[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18-Oct[/TD]
[TD="align: right"]02-Nov[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

SLA​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=SUMIF($B$2:$B$10,"<"&H3,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=COUNTIF($B$2:$B$10,"<"&H3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=I2/J2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=SUMIF($B$2:$B$10,"<"&H4,$F$2:$F$10)-SUM($I$2:I2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=COUNTIF($B$2:$B$10,"<"&H4)-SUM($J$2:J2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K3[/TH]
[TD="align: left"]=I3/J3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=SUMIF($B$2:$B$10,"<"&H5,$F$2:$F$10)-SUM($I$2:I3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J4[/TH]
[TD="align: left"]=COUNTIF($B$2:$B$10,"<"&H5)-SUM($J$2:J3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K4[/TH]
[TD="align: left"]=I4/J4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=C2-B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=C3-B3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=C4-B4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=C5-B5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=C6-B6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=C7-B7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=C8-B8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"]=C9-B9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D10[/TH]
[TD="align: left"]=C10-B10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(D2>E2,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=IF(D3>E3,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=IF(D4>E4,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]=IF(D5>E5,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]=IF(D6>E6,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F7[/TH]
[TD="align: left"]=IF(D7>E7,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]=IF(D8>E8,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"]=IF(D9>E9,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F10[/TH]
[TD="align: left"]=IF(D10>E10,1,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[donnalouiseb74]

Thanks for the answer, I'm struggling to make this work on in a calculated field in PowerPivot with the field names rather than the cell references.

 

[sijpie]

sorry, I know nothing of Powerpivot. But the formulas should work with named ranges as well.