1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Weekly Reporting

Discussion in 'Kayako reports (Kayako Classic)' started by Ken Adams, Dec 1, 2014.

  1. Ken Adams

    Ken Adams New Member


    Each Monday we have a meeting and the figures that are required are opened and closed tickets for the last week and the week before (2 weeks). I cannot seem to work out how the best way would be to produce this result. Thoughts anyone..??

  2. Matt O'R

    Matt O'R Member

    Hi Ken,

    You could use the Last7Days() function to get it for the last week, and then just collate the last two weekly reports.
  3. Vinay Sharma

    Vinay Sharma Staff Member

    Hello Ken,

    Per your requirements, I have created a sample KQL report which is as follows :

    SELECT Sum(IF(('Tickets.Total Replies' = 1) AND ('Tickets.Creator' = 'User' ),1,0)) As 'New Tickets', Sum(IF(('Ticket Audit Logs.Message' Like 'Ticket status changed from: % to: Closed' ),1,0)) As 'Closed Tickets' FROM 'Ticket Audit Logs','Tickets' WHERE 'Ticket Audit Logs.Creation Date' = ThisMonth() AND 'Tickets.Department' != '' GROUP BY Y('Tickets.Department'), Y('Tickets.Creation Date':Day) Totalize by Sum(IF('Ticket Audit Logs.Action' = 'New Ticket',1,0)),Sum(IF('Tickets.Total Replies' = 1,1,0)),Sum(IF(('Ticket Audit Logs.Message' Like 'Ticket status changed from: % to: Closed' ),1,0))
    The provided KQL query will generate the count of open tickets new tickets (tickets contain only single user reply), closed tickets (moved to Closed status). I am using 'ThisMonth() date function and it will cover last 2 weeks data. You may refer to different Date() functions to specify particular time frame.
  4. BJGGut3

    BJGGut3 Member

    Does this fit your need?

    select sum(if('tickets.creation date' = last7days(),1,0)) as 'Opened Last Week', sum(if('tickets.status' = 'closed' and 'tickets.last activity' = last7days(),1,0)) as 'Closed Last Week', sum(if(('tickets.creation date' >= date_sub(Today(),interval 14 day) and 'tickets.creation date' < date_sub(Today(),interval 7 day)),1,0)) as 'Opened Prior Week', sum(if('tickets.status' = 'Closed' and 'tickets.last activity' >= date_sub(today(),interval 14 day) and 'tickets.last activity' < date_sub(today(),interval 7 day),1,0)) as 'Closed Prior Week' from tickets group by 'tickets.department'
  5. Scott Jamieson

    Scott Jamieson New Member

    the TOTALIZE BY seem to be the wrong way round and the totalize function seems to be greater that the sum of the figures.

Share This Page