5 Replies Latest reply on Sep 14, 2018 10:45 AM by Hari Ankem

    Calculating Queue/Closure Rate

    Jason Ludwig

      Tableau Version 10.5.3

      Datasource: ServiceNow ODS (SQL Server Extract)

       

      I'm attempting to do something very basic that is proving to be very difficult.

       

      To provide our group managers and their leadership with an indicator of how efficiently the workforce is closing tickets we need to be able to calculate the number of tickets closed divided by the number opened within that same period (month over month).

       

      Seems easy enough, but when I place either the Closed or Opened datetime fields as a column I exclude some tickets that should be included.  Can anyone point me in the right direction for handling this?

        • 1. Re: Calculating Queue/Closure Rate
          Hari Ankem

          You need to use a Union query here. The first half of the Union can be to get you the Opened information, while the second half can be the Closed information. This will ensure that you have all the dates and nothing is really missed out.

          • 2. Re: Calculating Queue/Closure Rate
            Jason Ludwig

            I think you may have missunderstood.  My issue is not with the query.  The SQL query being used to give me task data already contains all the information I need.  Each task is a row and there are several date columns for when the task was opened, resolved, closed, etc.

             

            My issue is in the report's design interface - when I place either the opened or the closed datetime as a colum pill Tableau is automatically going to filter out everything else.  That's the problem.

             

            For example, if I pill opened as a column then each column would contain only those that are opened in that period. To calculate closed/opened for each month I would need to include all tasks.

            • 3. Re: Calculating Queue/Closure Rate
              Hari Ankem

              Let's assume your data is structured like this:

              IDOpen DateResolved DateClose Date
              11/1/181/10/182/12/18
              21/15/181/20/18
              32/1/183/2/184/15/18
              42/15/183/10/185/20/18
              53/1/18

               

              Now, if you use Open Date, you should have dates from January to March. But, if you use Resolved Date, you should have January and March only. And if you use Close Date, you should have February, April and May. But in reality, we should see all the 5 months, January to May irrespective of whether you are using Open, Resolved or Close dates. Do you agree?

               

              If yes, then you need to pivot your data as shown below:

              IDDateDate Type
              11/1/18Open Date
              21/15/18Open Date
              32/1/18Open Date
              42/15/18Open Date
              53/1/18Open Date
              11/10/18Resolved Date
              21/20/18Resolved Date
              33/2/18Resolved Date
              43/10/18Resolved Date
              12/12/18Close Date
              34/15/18Close Date
              45/20/18Close Date

               

              Now, you have only 1 column for Date to use and you should see all the 5 months. You can then use the Date Type to count how many tickets have been opened, resolved or closed.

               

              If you are convinced with the above solution, you need to do the similar thing with your data from SQL Server. And that's why I suggested that you do a Union. Assuming you have the same 3 date types as above, your Union should contain section for each of them.

              1 of 1 people found this helpful
              • 4. Re: Calculating Queue/Closure Rate
                Jason Ludwig

                Thanks for explaining that a little better.  Sadly, with my data containing millions on rows and spanning several datetime columns I don't know how feasible that kind of join is going to be - plus it would make my extract gigantic.  I'll see what I can do about creating a second dataset that only contains the PKID, the date and the action.

                • 5. Re: Calculating Queue/Closure Rate
                  Hari Ankem

                  Well, to reduce the volume of data, you need to aggregate, say at a monthly level, and then proceed in the suggest way.