5 Replies Latest reply on Nov 29, 2016 12:22 PM by Zach Leber

    Managing "Average Days Open" for tickets across a 12-month period

    Clint Mudrick

      Hello,

       

      I'm working with a dataset that has (in this simplified version) a set of ID's along with Open Dates and Closed Dates (note that some of these tickets are still open)

       

      What I'd like to do is create a graph showing the number of tickets opened at any given given time.  I've read through forum posts about making "Events" to flatten out the data further (one row for open, one for close, count the difference between the two) and I think it might be workable, but whether I use that method or others on the forums, I keep failing to create my X-Axis of dates.  When I try to add a new table of Excel-based dates, I've been unable to join that table to anything here.  I'm sure I'm missing something simple, but I've spent hours with no results.

       

      Mind taking a look and letting me know where I'm going astray?

       

      Thanks so much,

       

      Clint

        • 1. Re: Managing "Average Days Open" for tickets across a 12-month period
          Zach Leber

          Hi Clint, the TWBX you attached does not seem to contain an extract of the data, I am getting prompted to connect to your SQL server when I open it.

          • 2. Re: Managing "Average Days Open" for tickets across a 12-month period
            Clint Mudrick

            Sorry about that Zach.  Mind retrying?  I think I packaged it correctly this time

            • 3. Re: Managing "Average Days Open" for tickets across a 12-month period
              Zach Leber

              Thanks Clint, I got the data. I exported your extract to Excel then re-imported it using Custom SQL to split the rows. For Excel you can only do this on Windows with the Legacy Excel Connection but in practice you would be splitting from your database directly so could do this on Mac or Windows.

              Clint Custom SQL.png

               

              Then you can put [Date] on your X-axis and [Count] on your Y-axis and do a quick table calculation to get the running total of open tickets.

               

              Clint Running Total.png

              I've attached a 9.3 packaged workbook with your data and my modifications.

               

              Happy Thanksgiving.

              • 4. Re: Managing "Average Days Open" for tickets across a 12-month period
                Clint Mudrick

                Thanks Zach, that was very helpful!  I hadn't thought of putting it together in that fashion.

                 

                One more thing though...can you think of a way to take the above data and create an "Average Age of Open Tickets" that can trend along with the running total?  Ideally I'd like it to be able to tell me that--of those tickets open in January, the average age of those tickets (Now() - CreatedDate) was 22 days, while in May it was 17 days.

                 

                Is that even possible?  I've been pulling my hair out trying to make it happen and no luck thus far.

                 

                Thank you again, and all the best

                • 5. Re: Managing "Average Days Open" for tickets across a 12-month period
                  Zach Leber

                  Splitting open/close records into two rows each is how we do all our queue tracking.

                   

                  I was hoping you weren't going to ask about calculating the retrospective average age of open tickets in past months, that's a much harder problem, but you may be able to answer a different question instead, how long did it take to close tickets in past months. I've added that to the workbook as row 3, with details per ticket in row 4. Then you can see if average close time is correlated with average number of open tickets (it does appear to be). I tried using dual axis for rows 3 and 4 but your outliers dominate the y-axis so I turned that off.

                   

                  I also added delta bars on row 2, these are a signature feature of ours, it shows you what is causing the rise or drop in open tickets, e.g. lots of new tickets or not many closed tickets. Plus it highlights the number of tickets opened in past months that are Still Open.

                   

                  I noticed that your raw data uses a date of 1/1/1970 to indicate NULL so added a couple of filters/calculations to handle that. Screenshot embedded below and updated workbook attached.

                   

                  ClintTix3.png