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

# Calculate tickets created, closed and open at month end

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?

• ###### 1. Re: Calculate tickets created, closed and open at month end

For the open count a common solution is to join your data table to a generic date table with TWO join clauses:

Start Date/Hour <= Generic Date/Hour

End Date/Hour > Generic Date/Hour

This will create an active record for each day that it was active (or open) which you can do a simple count, although it will dramatically increase your record count.

For the # created and closed you will also want to pivot your date fields into a single date field so you can count the opened and closed along one date dimension.

• ###### 2. Re: Calculate tickets created, closed and open at month end

for open tickets you mean to say take self join ?

if possible could u please share sample sql/tableau file ?

• ###### 3. Re: Calculate tickets created, closed and open at month end

No not a self join, a join on a generic date table that will create a record for each ticket and for each day that it was open.  Here is a Tableau training workbook in which the first tab this was done.

• ###### 4. Re: Calculate tickets created, closed and open at month end

We don't have generic date table, generic date table would unnecessary I think(correct me with better way), Other way to achieve same ?

• ###### 5. Re: Calculate tickets created, closed and open at month end

There is a way to do it through a running sum created minus running sum closed but this will not pick up any tickets that were created before your date filter.