2 Replies Latest reply on Dec 15, 2016 4:52 AM by Paula Sims

    How to calculate point in time totals and then display

    Paula Sims

      We have raw data that shows IT ticket incidents by incident type, start and end. What I need to is to calculate is best shown as follows:

       

      Incident Type

      # of tickets open as of Today's Date

      (ie 12/14/2016)

      # of tickets open as of Most Recent Qtr End

      (ie 9/30/2016)

      # of tickets open as of Most Recent Quarter End - 1 Quarter

      (6/30/2016)

      # of tickets open as of Most Recent Quarter End - 2 Quarter

      (3/31/2016)

      # of tickets open as of Most Recent Quarter End - 2 Quarter

      (12/31/2015)

      # of tickets open as of Most Recent Quarter End - 2 Quarter

      (9/30/2016)

      IT Hacking53810151
      User Installs15156103
      Password Resets201510550
      TOTAL IT TICKETS OPEN40192321304

       

       

      My raw data in the SQL table looks like this

       

      Incident TypeStart DateEnd DatePerson
      IT Hacking1/1/201612/31/9999Jane Doe
      IT Hacking6/3/201612/10/2016Happy Gilmore
      Password Resets12/10/201612/14/2016Mary Poppins

       

      I do get the result above in Excel but it's tedious. I'd like to be able to do it in Tableau. Can I do it with SQL statement(s) with dynamic date changes beginning with the run date (current date) and then the most recent 5 quarter ends back? I'd prefer not to have to feed it into Tableau but rather a direct calculation.

       

      Also, if  you can suggest a graphical representation other  than a bar chart, that would be appreciated. It's not really a gantt chart but it sort of is.

       

      Thanks