1 2 Previous Next 15 Replies Latest reply on May 24, 2019 3:40 PM by Jonathan Drummey

    Weekly Ticket Backlog Tracking

    Galen Flynn



      I am looking to create a chart to track our tickets created vs solved during a week as well as the total number of open tickets. The requirements are a chart looking like:



      I'm not really sure how to go about this since it seems I have to create some kind of independent date value. In this example for week ending 10/14 (10/08-10/14) there were 69 tickets created, 70 solved and 605 open tickets (tickets in backlog). I'm not necessary looking for the same format, but I am wanting to compare all three values together somehow.


      I've attached a sample workbook and any help would be great. Thank you very much.



        • 1. Re: Weekly Ticket Backlog Tracking
          ShivaRam Chennapragada

          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.




          • 2. Re: Weekly Ticket Backlog Tracking
            Galen Flynn

            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!

            • 3. Re: Weekly Ticket Backlog Tracking
              Galen Flynn



              I'm wondering if anyone had any thoughts. Thank you!

              • 4. Re: Weekly Ticket Backlog Tracking
                Zach Leber

                Hi Galen,


                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)$']


                UNION ALL


                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.



                Ticket Backlog.png

                Tableau 10.4 TWBX attached.




                • 5. Re: Weekly Ticket Backlog Tracking
                  ShivaRam Chennapragada

                  Hi Galen,


                  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.




                  • 6. Re: Weekly Ticket Backlog Tracking
                    Galen Flynn



                    This is exactly what I was looking for. Thank you so much for your help!

                    • 7. Re: Weekly Ticket Backlog Tracking
                      Alex Blakemore

                      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.

                      • 8. Re: Weekly Ticket Backlog Tracking
                        Zach Leber

                        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?

                        • 9. Re: Weekly Ticket Backlog Tracking
                          Jonathan Drummey

                          Hi Zach,


                          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?



                          2 of 2 people found this helpful
                          • 10. Re: Weekly Ticket Backlog Tracking
                            Zach Leber

                            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.

                            • 11. Re: Weekly Ticket Backlog Tracking
                              Galen Flynn

                              Hi Jonathan,


                              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!


                              • 12. Re: Weekly Ticket Backlog Tracking
                                Zach Leber

                                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.



                                calculated fields.pngdelta queue.png

                                • 13. Re: Weekly Ticket Backlog Tracking
                                  Jonathan Drummey

                                  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

                                  CASE [Source]

                                      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.



                                  • 14. Re: Weekly Ticket Backlog Tracking
                                    Galen Flynn

                                    Thank you both for the help! This technique makes sense, but I am having trouble on the Union portion. I am using the 1 = 2 technique, but coming up with the error:


                                    1 2 Previous Next