Would this work?
I've created a Week End Date using Request Date field that buckets 7 days into week, then visualize Closed v Open tickets with Total tickets as reference lines. You can modify the calc or view as you want. Attached workbook.
Sample_Set.twbx 47.6 KB
Thank you for the prompt response Shiva,
Unfortunately, this isn't exactly what I'm looking for. Since you calculated the week ending based on 'Request Date', for each given week this will only display:
- Tickets that remain open that were created during the week
- Tickets closed that were created during the week
- Total Tickets (Open or Close)
I am looking for three independent values for each week:
- Tickets Created
- Tickets Solved (Regardless of when they are created)
- Total Backlog (All tickets that remain open up until that week)
Please let me know if you need further clarification. Thank you!
I'm wondering if anyone had any thoughts. Thank you!
If you search the forums for "ticket counting" you will see that you need to re-shape your data using Custom SQL to get a running total. I exported your data to Excel so I could use the Windows-only legacy Custom SQL option but if you are connecting to a database you can use Custom SQL more easily.
SELECT ['Sample_Set (285212)$'].[Request date] AS [date],
"Requested" as [status],
+1 as [total]
FROM ['Sample_Set (285212)$']
SELECT ['Sample_Set (285212)$'].[Solved date] AS [date],
"Solved" as [status],
-1 as [total]
FROM ['Sample_Set (285212)$']
I use bi-directional bars instead of side-by-side bars to track opened and closed tickets. I don't know how to combine a running total with side-by-side bars.
You'll also need to use a table calc filter for date filtering to preserve the running sum.
Tableau 10.4 TWBX attached.
Ticket Backlog (285212).twbx 41.9 KB
Sorry I totally forgot about this, in my approach I considered request date to keep it simple. Looks like you'd need a third date dimension that combines Request and Closed dates. Zach's approach with UNIONs really does that. Would you be able to pivot your data and try his approach?
@Zach- correct me if I'm wrong.
This is exactly what I was looking for. Thank you so much for your help!
FYI, You can, of course, do the same thing using Tableau's union feature and some calculated fields, instead of using custom SQL.
Its not really critical in this case, but Tableau can generate more efficient queries if you limit the amount of custom SQL you write.
Hi Alex, could you provide an example of how to use union instead of Custom SQL to take data in the form (ticket, opened, [closed]) and compute monthly stats and a running queue?
2 of 2 people found this helpful
Tableau's native Union feature generates a Table Name field for each table in the union which can then be used in Date, Status, and Total calculated fields that generate the values you had in the custom SQL from your earlier post. For example here's a Date field:
CASE [Table Name]
WHEN 'software cases 2018.csv' THEN [Created Date]
WHEN 'software cases 2018.csv1' THEN [Closed Date]
The Status and Total fields could be quickly built by duplicating the date field and then using 1, -1 and 'open', 'closed', respectively.
Does that make sense?
Thanks Jonathan, I see how Tableau's built-in union feature can replicate what we're doing with Custom SQL's union feature. When we started ticket tracking there was no Tableau union feature but now that there is, and Custom SQL isn't being supported for files anymore (and never was for Mac), it looks like a better approach.
Sorry for the late response, but I don't quite understand how to do this. Is it possible to walk me through the process? We just upgraded to Tableau 2019.1.3, so I'd like to phase out custom queries. Thank you!
Hi Galen, here's my method modified per Jonathan's advice to use UNION instead of Custom SQL.
1. Edit your data source and union your ticket list with itself, doubling the record count
2. Use the automatically created [Table Name] field to distinguish one copy of the records from the other
3. Create calculated fields for Date and Delta as depicted and attached below.
4. Set up a running total on Delta, optionally show the individual monthly deltas as well
Note this doesn't use any date scaffolding so requires that your ticket density is greater than your display date density, e.g. at least one ticket per month if you are displaying months.
Hah! Zach, were answering the same time I was, here's my contribution.
I used the data from the original workbook, since it was extracted I had to use the "full outer join with join calc on 1=2 to simulate a union" technique covered in Dual Axis Mapping - Many Ways .
Then I generated a Source field along with a Date and Total Opened/Closed field.
Here's the Total Opened/Closed field, which uses an IF statement so we don't count accidentally count cases w/Null dates, for example cases that aren't actually closed:
// this uses the duplicated data so we can appropriately add open cases
// and subtract closed cases
WHEN 'Open' THEN IF NOT ISNULL([Date]) THEN 1 ELSE 0 END
WHEN 'Closed' THEN IF NOT ISNULL([Date]) THEN -1 ELSE 0 END
v2019.1 workbook is attached.
Zach, re: your point about displaying a mark for each day/week/month even when there is no data, the "simple" solution is to turn on Show Missing Values, but it has some extra complexities when we're using continuous axes. I set something up in the workbook.