This becomes easy if you use Custom SQL to reshape your data. There are many forum posts describing how to do this, here's my advice, we do this type of counting a lot.

1. connect to your ticket data source and switch to Custom SQL
2. split each original ticket record into a start and end record using UNION ALL as depicted
3. use a running sum table calculation to show the total count open at any time

Depending on the density of your data there may be date periods in which no tickets were opened or closed. You could pad your data but we prefer to use delta bars instead of lines that may be discontinuous. Attached workbook is from Tableau 8.1.

Thanks for pointing out that because the data connection process is different in 8.2, the way to get to Custom SQL is different than depicted and varies depending on data source type. But once you get to Custom SQL, the SQL to duplicate the records and the Tableau view building process remain the same.

TThanks for this! Been struggling with the same problem!

Hello Zach

Thanks for this example, it helped me get tickets opened and closed. A question I have that maybe you can consider is if in the rows you also had a field for the ticket called Status which could be open, closed, in progress etc. Would this double the count for each one if you wanted to have a different visualization? Correct me if I am wrong but if you do the Union All doesn't that create 2 rows for each record which double the count?

Thanks

Hi David, I think what you're asking is how to separately plot the number of tickets that were open vs. in progress at any time in the past. For that you'd need to maintain transition times when the ticket was moved from open to in progress to closed. There are several ways of storing that data, I've built a demo using one. UNION ALL does create additional rows but the +1/-1 convention means the ticket is only in one state per day. I turned on grand totals in the example to show the number of non-closed tickets. Adding closed tickets doesn't really fit here as that number keeps going up and we're plotting the number of tickets in a given state on a given day, not the number that transitioned to a given state on a given day. Regards, Zach.

Raw data

Custom SQL (legacy Excel syntax)

SELECT [Sheet1$].[Open] AS [Date],

    [Sheet1$].[Ticket] AS [Ticket],

    "Open" AS [Status],

    +1 AS [Count]

FROM [Sheet1$]

UNION ALL

SELECT [Sheet1$].[In Progress] AS [Date],

    [Sheet1$].[Ticket] AS [Ticket],

    "Open" AS [Status],

    -1 AS [Count]

FROM [Sheet1$]

UNION ALL

SELECT [Sheet1$].[In Progress] AS [Date],

  [Sheet1$].[Ticket] AS [Ticket],

   "In Progress" AS [Status],

    +1 AS [Count]

FROM [Sheet1$]

UNION ALL

SELECT [Sheet1$].[Closed] AS [Date],

  [Sheet1$].[Ticket] AS [Ticket],

   "In Progress" AS [Status],

    -1 AS [Count]

FROM [Sheet1$]

Resulting Workbook (10.3 TWBX attached)