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.

Monthly Reporting by Type

Discussion in 'Using Kayako Classic' started by Kate Stoan, Aug 31, 2017.

  1. Kate Stoan

    Kate Stoan New Member

    I am trying to generate a report that shows the number of tickets active at the end of each month, the number of tickets opened during that month and the number of tickets closed during that month broken down by "Type."

    I was able to create this report which provides me the breakdown of active, opened and closed but doesn't provide me with the flexibility to check months that have already passed and does not provide me with the breakdown by 'Type."
    • select sum(if('tickets.status' != 'Closed',1,0)) as 'Open Tickets', sum(if('tickets.creation date' = ThisMonth(),1,0)) as 'New Tickets', sum(if('tickets.status' = 'closed' AND 'tickets.last activity' = ThisMonth(),1,0)) as 'Closed Tickets' from tickets group by 'tickets.department'
    I was also able to create this report which provides me with the breakdown month over month and reflects the type, but does not show how many of each type were opened vs closed vs active at the end of the month.
    • SELECT Count('Tickets.Ticket Mask ID') FROM 'Tickets' WHERE 'Tickets.Creation Date':Year ='2017' GROUP BY X('Tickets.Creation Date':MonthName), Y('Tickets.Type') ORDER BY 'Tickets.Creation Date' TOTALIZE BY Count('Tickets.Ticket Mask ID')
    I tried to merge the two together by creating this report. While the layout is exactly what I want, it is not providing accurate counts of the tickets.
    • select sum(if('tickets.status' != 'Closed',1,0)) as 'Open Tickets', sum(if('tickets.creation date' = ThisMonth(),1,0)) as 'New Tickets', sum(if('tickets.status' = 'closed' AND 'tickets.last activity' = ThisMonth(),1,0)) as 'Closed Tickets' from tickets GROUP BY X('Tickets.Creation Date':MonthName), Y('Tickets.Type') TOTALIZE BY Count('Tickets.Ticket Mask ID')
    Any help you can provide is greatly appreciated!

    Warmest Regards,

    Kate
     

Share This Page