5 Replies Latest reply on Nov 8, 2017 10:10 AM by John Sobczak

    Calculate tickets created, closed and open at month end

    satish bhor

      we have ticketing system with following sample data

       

      Date Format YYYY-MM-DD

       

      id created_date state          state_entered_date

      t1  2017-01-01  new            2017-01-01

      t1  2017-01-01  in progress 2017-01-10

      t1  2017-01-01  review         2017-03-11

      t1  2017-01-01  closed         2017-03-14

      t2  2017-01-04  new            2017-01-04

      t2  2017-01-04  in progress 2017-01-10

      t2  2017-01-04  review         2017-02-11

      t2  2017-01-04  closed         2017-02-14

      t3  2017-01-11  new            2017-01-11

      t3  2017-01-11  in progress 2017-01-14

      t3  2017-01-11  review         2017-01-22

      t3  2017-01-11  closed         2017-01-27

      t4  2017-01-14  new            2017-01-14

      t4  2017-01-14  in progress 2017-02-02

      t4  2017-01-14  review         2017-03-22

      t4  2017-01-14  closed         2017-03-27

      t5  2017-02-14  new            2017-02-14

      t5  2017-02-14  in progress 2017-03-02

      t5  2017-02-14  review         2017-03-22

      t5  2017-02-14  closed         2017-04-27

       

      We need to calculate following metrics in tableau,

       

      1) Total tickets created in month

      2) Total tickets closed in month

      3) Total tickets still open(not closed) at month end(overall backlog from all past months)

       

      date      total_created total_closed total_open_month_end

      JAN-2017           4                  1                  3

      FEB-2017           1                  1                  3

      MAR-2017          0                  2                  1

      APR-2017           0                  1                  0

       

      How to acheive this is in tableau?