3 Replies Latest reply: Aug 18, 2018 3:05 AM by Tim Evans RSS

    Ticket Trend - How to Count New, Open and Closed Tickets

    Tim Evans

      Hi All,

       

      I am new to QLIK Sense and after searching the forums for the best way to report on the trend of New, Open and Closed service desk tickets I have reached the limit of my capabilities and am seeking some guidance and help.

       

      Each record has a Date Opened and Date Closed field and I would like to trend the volume of tickets by New, Opened and Closed over periods of time.

       

      The method I'm using is to create a related table and flag count, see below:

       

      TicketTrend:

      LOAD *,

      MonthName(TrendDate) as TrendMonthName

      WHERE TrendDate <= Today();

      LOAD

        [Ticket No],

        Date([Date Opened]+iterno()-1, 'DD/MM/YYYY')  as TrendDate,

          If(iterno()=1, 1,0) as Flag_New,

          If([Date Opened]+iterno()-1 = [Date Closed],1,0) as Flag_Closed,

          If([Date Opened]+iterno()-1 < [Date Closed],1,0) as Flag_Open

      Resident [SN Tickets]

      WHILE [Date Opened]+iterno()-1 <= [Date Closed];

       

      The problems I have are:

      1. How do I manage NULL Closed Dates - If the Closed Date is NULL no record is added to the TicketTrend table

      2. The operation to update Flag_Closed is never successful

       

      I have attached a sample source data file and app so you can see where I am at.

       

      Look forward to learning from this problem.

       

      Thanks in advance.

      Tim

        • Re: Ticket Trend - How to Count New, Open and Closed Tickets
          David Forest

          A solution would be to use IntervalMatch of the Open to Closed (or Today() if Closed is null) against a MasterCalendar generating a Flag_Closed, Flag_Open or a State Field with the value of Open or Closed per ticket per day.

          This allows to total opened/closed stats for any day, week. month, quarter, year by looking at the last value for a ticket in the selected date range.

          So for month end Jan 31, every ticket will have a value of opened or closed; yes this means tickets live forever and the database will grow, but Qlik works fairly quickly with this in my experience.

          • Re: Ticket Trend - How to Count New, Open and Closed Tickets
            Tim Evans

            After a little more time I have solved my problems.

             

            1. Added a test for NULL Date Closed.

             

            2. The date comparison of Date Opened to Date Closed was not working because my dates were timestamped.  I resolved this by converting the dates to date only in the load script.

             

            Revised Script:

            TicketTrend:

            LOAD *

            WHERE %KeyDate <= Today();

            LOAD

              [Ticket No],

              Date([Date Opened]+iterno()-1)  as %KeyDate,

                If(iterno()=1, 1,0) as Flag_New,

                If(len(trim([Date Closed]))>0, If([Date Opened]+iterno()-1 = [Date Closed],1,0),'0') as Flag_Closed,

                If(len(trim([Date Closed]))>0, If([Date Opened]+iterno()-1 <= [Date Closed],1,0),'1') as Flag_Open

            Resident [SN Tickets]

            WHILE [Date Opened]+iterno()-1 <= [Date Closed];

             

            I have linked this table to my Master Calendar by the %KeyDate field.