Complex Missed/Met Calculation

[StandardSanders]

I am working on a report that will show a percentage of tickets my team is able to resolve within 24 hours, minus any hold time, that no other teams touch and that we do not reopen.

Here is an example data set of a single ticket:

[TABLE="width: 1235"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ticketid[/TD]
[TD]creationdate[/TD]
[TD]changedate[/TD]
[TD]accumulatedholdtime[/TD]
[TD]created by group[/TD]
[TD]hsescalate_text[/TD]
[TD]externalsystem[/TD]
[TD]cinum[/TD]
[TD]description[/TD]
[TD]status[/TD]
[TD]ownergroup[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]QUEUED[/TD]
[TD]2nd LVL[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]QUEUED[/TD]
[TD]LVL1[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]QUEUED[/TD]
[TD]2nd LVL[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]QUEUED[/TD]
[TD]LVL1[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]RSLVDCONF[/TD]
[TD]LVL1[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]RESOLVED[/TD]
[TD]LVL1[/TD]
[/TR]
[TR]
[TD]SR123456[/TD]
[TD="align: right"]1/2/2015 7:45[/TD]
[TD="align: right"]1/9/2015 14:58[/TD]
[TD="align: right"]30[/TD]
[TD]LVL1[/TD]
[TD]N[/TD]
[TD]PHONECALL[/TD]
[TD]APPLICATION[/TD]
[TD]ERROR CODE[/TD]
[TD]CLOSED[/TD]
[TD]LVL1[/TD]
[/TR]
</tbody>[/TABLE]

Column A is just the ticket number, Column B is when the ticket was created, C is closed time (this 7 days after being in RSLVDCONF status), D is the hold time in minutes, E is the group that created the ticket, F is if the ticket was escalated into an Incident (would be a miss), G is how the contact came to us (phone, email, walkby) H is the application the call is about and I the type of error, J is the status history and K is the group who had control of it at that point.

I am having trouble getting is for it to count a miss in two scenarios.

Scenario 1: LVL1 team gives control of ticket to another group and then the ticket comes back to LVL 1 team. This should be a miss but I'm not able to get this to calc correctly.

Scenario 2: LVL 1 team sets the ticket status to RSLVDCONF more than once for that ticket. This would show the ticket has been re-opened and therefore not a first contact close.

Ideally these would be misses and I could see which tickets were misses based on this and the other scenarios which I have already got working.

Let me know if I need any additional clarifications for this.
Thanks,
Mike

 

[StandardSanders]

I figured out Scenario 1, I just added a column to the data that says if the created ownergroup = ownergroup then 1 otherwise 0. Then using a distinct count of times that field is 0 and the ownergroup is LVL1. Now I Just need to figured out Scenario 2.

 

[StandardSanders]

and got the other one figured out had to do basically this mess: calculate(counta(Table1[ticketid]),Table1[status]="RSLVDCONF", Table1[assignedownergroup]="AM-I-TSC",Table1[hsescalate_text]="N",Table1[Time]<1,Table1[XFER]=1)-calculate(distinctcount(Table1[ticketid]),Table1[status]="RSLVDCONF", Table1[assignedownergroup]="AM-I-TSC",Table1[hsescalate_text]="N",Table1[Time]<1,Table1[XFER]=1)

In case anyone runs into something similar thought I'd post what I sauced out.