6 Replies Latest reply on Mar 1, 2018 1:33 AM by Ivana Foukalova

    Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

    Ivana Foukalova

      Good morning!

       

      The below visualization is a significant challenge for me to replicate in Tableau as after having explored the forum and Tableau help and trying several approaches using various LODs, scaffold, dual x-axis..., I was not able to get much closer to the wanted result.

       

      GOAL is to calculate “tickets closing speed” with x axis showing number of days and y axis showing 100% - ratio of tickets closed within n days of opening vs. all tickets that could have been closed within n days. Sample  data and outcome as per picture below (several first records only) and attached Excel (full sample data set and related calc.).

         

      Original DatasetCalculations
      Ticket IDTicket Opened DateTicket Closed DateMaxReportDateTicket Closed (1 = yes, 0 = no)Days Ticket Opened to MaxReportDateTicket Age
      12/4/20172/7/20172/8/2017143
      21/30/20172/7/20172/8/2017198
      31/29/2017 2/8/201701010
      41/28/2017 2/8/201701111
      51/27/20171/30/20172/8/20171123
      61/22/20171/23/20172/8/20171171
      71/17/20171/17/20172/8/20171220

       

      Aggregation
      Days to closeAll relevant ticketsTickets ClosedClosing Speed
      017194%
      117382%
      217382%
      317571%
      417665%

       

        

      The data set is simplified as in reality:

      - there are attributes related to each ticket that I would like to use as filters

      - may be working with date&time instead of days

      - I would also like to show different lines for different periods of time, i.e. to compare each tickets closing speed for tickets opened in last 14 days vs. last 30 days (or last quarter....)

       

      Any advice/hint on the best approach to this problem will be truly very much appreciated.

       

      Thank you and best regards,

      Ivana