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
1	Ticket Nr	Date started	Date Closed	Duration	Agreed	Over		Month	Counts Over	Total Tickets	% On Time
2	1	01-Aug	06-Aug	5	12	0		01-Aug	1	3	33%
3	2	05-Aug	21-Aug	16	12	1		01-Sep	1	2	50%
4	3	12-Aug	18-Aug	6	12	0		01-Oct	2	4	50%
5	4	03-Sep	05-Sep	2	12	0		01-Nov
6	5	04-Sep	21-Sep	17	12	1
7	6	07-Oct	24-Oct	17	12	1
8	7	08-Oct	10-Oct	2	12	0
9	8	14-Oct	18-Oct	4	12	0
10	9	18-Oct	02-Nov	15	12	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

SLA​

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

<tbody>
</tbody>

 

[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.