6 Replies Latest reply on May 8, 2018 1:29 PM by Zach Leber

    Model tickets open vs closed

    Galen Dresser

      I've got a data set that has ticket information detailing (among other things) when a ticket was open and when it was closed. I would like to create a graph that shows the number of tickets opened in a month as well as the number of tickets closed.  I am new to Tableau and have tried several approaches to this but I am not getting anywhere.  Any pointers?

       

      Example data

      Ticket #Date OpenedDate Closed
      12-143-14
      23-143-14
      34-146-14

       

      Example graph

      graphEx.PNG

        • 1. Re: Model tickets open vs closed
          Zach Leber

          This becomes easy if you use Custom SQL to reshape your data. There are many forum posts describing how to do this, here's my advice, we do this type of counting a lot.

          1. connect to your ticket data source and switch to Custom SQL
          2. split each original ticket record into a start and end record using UNION ALL as depicted
          3. use a running sum table calculation to show the total count open at any time

          Depending on the density of your data there may be date periods in which no tickets were opened or closed. You could pad your data but we prefer to use delta bars instead of lines that may be discontinuous. Attached workbook is from Tableau 8.1.

          Capture.PNG

          Capture 2.PNG

          • 2. Re: Model tickets open vs closed
            Galen Dresser

            This seems like the best course of action, marking as correct with the caveat that this method has changed for Tableau 8.2.

            • 3. Re: Model tickets open vs closed
              Zach Leber

              Thanks for pointing out that because the data connection process is different in 8.2, the way to get to Custom SQL is different than depicted and varies depending on data source type. But once you get to Custom SQL, the SQL to duplicate the records and the Tableau view building process remain the same.

              • 4. Re: Model tickets open vs closed
                Kirstin Lyon

                TThanks for this! Been struggling with the same problem!

                • 5. Re: Model tickets open vs closed
                  David Matthews

                  Hello Zach

                   

                  Thanks for this example, it helped me get tickets opened and closed. A question I have that maybe you can consider is if in the rows you also had a field for the ticket called Status which could be open, closed, in progress etc. Would this double the count for each one if you wanted to have a different visualization? Correct me if I am wrong but if you do the Union All doesn't that create 2 rows for each record which double the count?

                   

                  Thanks

                  • 6. Re: Model tickets open vs closed
                    Zach Leber

                    Hi David, I think what you're asking is how to separately plot the number of tickets that were open vs. in progress at any time in the past. For that you'd need to maintain transition times when the ticket was moved from open to in progress to closed. There are several ways of storing that data, I've built a demo using one. UNION ALL does create additional rows but the +1/-1 convention means the ticket is only in one state per day. I turned on grand totals in the example to show the number of non-closed tickets. Adding closed tickets doesn't really fit here as that number keeps going up and we're plotting the number of tickets in a given state on a given day, not the number that transitioned to a given state on a given day. Regards, Zach.

                     

                    Raw data

                    multi-state data.png

                    Custom SQL (legacy Excel syntax)

                    SELECT [Sheet1$].[Open] AS [Date],

                        [Sheet1$].[Ticket] AS [Ticket],

                        "Open" AS [Status],

                        +1 AS [Count]

                    FROM [Sheet1$]

                     

                    UNION ALL

                     

                    SELECT [Sheet1$].[In Progress] AS [Date],

                        [Sheet1$].[Ticket] AS [Ticket],

                        "Open" AS [Status],

                        -1 AS [Count]

                    FROM [Sheet1$]

                     

                    UNION ALL

                     

                    SELECT [Sheet1$].[In Progress] AS [Date],

                      [Sheet1$].[Ticket] AS [Ticket],

                       "In Progress" AS [Status],

                        +1 AS [Count]

                    FROM [Sheet1$]

                     

                    UNION ALL

                     

                    SELECT [Sheet1$].[Closed] AS [Date],

                      [Sheet1$].[Ticket] AS [Ticket],

                       "In Progress" AS [Status],

                        -1 AS [Count]

                    FROM [Sheet1$]

                     

                    Resulting Workbook (10.3 TWBX attached)

                    multi-state view.png