-
1. Re: Model tickets open vs closed
Zach Leber Aug 9, 2014 4:14 AM (in response to Galen Dresser)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.
- connect to your ticket data source and switch to Custom SQL
- split each original ticket record into a start and end record using UNION ALL as depicted
- 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.
-
Ticket Counting.twbx 28.1 KB
-
Tickets.xlsx 9.2 KB
-
2. Re: Model tickets open vs closed
Galen Dresser Aug 15, 2014 2:39 PM (in response to Zach Leber)This seems like the best course of action, marking as correct with the caveat that this method has changed for Tableau 8.2.
-
3. Re: Model tickets open vs closed
Zach Leber Aug 15, 2014 6:34 PM (in response to Galen Dresser)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.
-
4. Re: Model tickets open vs closed
Kirstin Lyon Apr 17, 2015 9:51 PM (in response to Galen Dresser)TThanks for this! Been struggling with the same problem!
-
5. Re: Model tickets open vs closed
David Matthews May 8, 2018 12:22 PM (in response to Zach Leber)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
-
6. Re: Model tickets open vs closed
Zach Leber May 8, 2018 1:29 PM (in response to David Matthews)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)
-
Multi-state.twbx 24.9 KB
-