by tomas.ferko@keboola.com Let's say you're dealing with a fairly simple SLA reporting situation in which you are bucketing tickets according to their “First reply time” with several metrics and breaking them down by Week/Ticket solved i.e. using metrics like: # Tickets SELECT COUNT(Ticket Id,TicketTagId) WHERE Ticket Status <> Deleted AND Ticket Tag Deleted Flag <> true # Tickets (First response < 1h) SELECT # Tickets WHERE (SELECT SUM (First reply time in minutes) BY Ticket Id ALL OTHER) < 60) etc. During validation you might encounter that the total number of the tickets you have broken down into the respective SLA buckets by First Reply Time do not actually tally up to the total amount of Tickets Solved that week. If you got the metrics right and used the operators correctly the reason for this is very likely that not all solved tickets have a “First reply time” as the agent might have marked them as Solved at some point without ever posting a public reply to it. In such a case however it might make sense to report on such tickets according to their “Full Resolution time” and bucket them in with the other ones within the same report. In such a case you need to first create a metric that counts the tickets that have a NULL value for their “First reply time” # Tickets (NULL reply time) SELECT # Tickets WHERE (IFNULL ((SELECT SUM(First reply time in minutes) BY Ticket Id ALL OTHER),-1)) = -1 Note the negative values. If we would for example use something like this: SELECT # Tickets WHERE (IFNULL ((SELECT SUM (First reply time in minutes) BY Ticket Id ALL OTHER), 1)) = 1 .. we would run into problems because then we would count the tickets that have “First Reply Time In Minutes” = 1 as well. Since -1 cannot be a real FRT value it's what we have to use in order to get correct results. Now we have to build SLA bucketing metrics for the Tickets with NULL in First Reply Time, reply time and divide the according to full resolution time i.e. : # Tickets NULL_FRT (Full Resolution < 1h) SELECT # Tickets (NULL Response time) WHERE (SELECT SUM (Full resolution time in minutes) BY Ticket Id ALL OTHER) < 60 etc… … and wrap it all up with the final metric : SLA < 1h SELECT IF # Tickets NULL_FRT (Full Resolution < 1h) = 0 THEN # Tickets (First response < 1h) ELSE # Tickets NULL_FRT (Full Resolution < 1h) + # Tickets (First response < 1h) END |