1 2 Previous Next 22 Replies Latest reply on Jun 13, 2015 11:05 AM by Joe Mako

    Trending Point in Time Data

    . Derek

      I am trying to trend out the number of "open" support tickets by month (e.g. the number of open tickets as of Jan was X, and as of Feb was Y). Is it possible to create a calculated field that will give me the count of records each month in my data set?


      I would like to be able to plot two lines one line for the number of open tickets at month end and a second line for the number of completed tickets in the month for a time period that dates back 4 years.


      Do I need to have an extract of the data from each of those points in time or is it possible to achieve this by looking at the most recent data set?

        • 1. Re: Trending Point in Time Data
          Joe Mako



          There are certainly some interesting questions that Tableau visualize when connecting to data from a support ticketing system. The first question would be what is the structure of the data:


          Is it a ticket log like:

          Ticket ID | Date Open | Date Close


          or a transaction log like:

          Ticket ID | Date | Action

          • 2. Re: Trending Point in Time Data
            James Baker

            Seems like this could be very similar to the currently hot topic: http://www.tableausoftware.com/forum/displaying-employment-gaps-organization


            Both involve determining status at any point in time given start/end data points.

            • 3. Re: Trending Point in Time Data
              Richard Leeke

              Yes, very similar to that thread.


              Joe's question above is a very good one, that structure affects the best way forward.


              It's definitely worth looking at what Joe did on that other thread.  It's actually not all that easy to see in his posted workbook because he had to create an extract (presumably because he needed the COUNTD() function), so you can't easily see what he did to the data.  Basically he added a table of dates with one row per day.  That allowed joining based on the start-end date range, effectively giving one row per day per source data row.  You might well find a similar approach is the easiest here - assuming you can augment your data source with a dates table.

              • 4. Re: Trending Point in Time Data
                . Derek

                There are a few challenges here.


                1.) Our support system is a Lotus Notes database so I am forced to export the data from LN to Excel and then connect to the spreadsheet.


                2.) The log looks like the first example you gave (Ticket ID | Date Open | Date Close)


                I have not taken a close look at the other thread but will do so and see if that helps.


                I appreciate the suggestions.

                • 5. Re: Trending Point in Time Data
                  Alex Blakemore

                  Here's another thread you might want to look at.




                  I used something similar to plot the number of open work items over time. The custom SQL described in that thread produces a table of date, delta pairs that allows Tableau to use a running total table calc to show the queue length over time.


                  In the end, I found it useful to also do the running total in the custom SQL using Oracle's analytic queries - something like sum(delta) over (order by ticket_date). Then Tableau sees a nice table of date, queue length pairs.


                  One problem is that if you have days without activity where no jobs arrive or depart, such as weekends, then you won't have a row in your data source for those days. So if you're computing the average over time, or want a line plot that shows # of open tickets each day, it may not behave as you want.


                  One way to avoid that is to use SQL's UNION ALL statement to also include a table with one row per day in your range that has a delta of zero prior to performing the running total.

                  • 6. Re: Trending Point in Time Data
                    Richard Leeke

                    A couple more things on top of Alex's comments.


                    The UNION ALL to ensure all rows you need are present in your result set needs to be with a table at the lowest level of granularity that you need for your reporting.  So daily data as Alex mentions may be all (or even more than) you need - or you might need data down at the hour or minute if you are monitoring highly dynamic data and want to be able to drill down to that level of granularity, whilst ensuring no gaps in your timeline.



                    This isn't relevant to your Notes/Excel data source, but for anyone working in the Oracle environment I *think* that these dummy rows could be a classic use for the Oracle "Model Clause".  I've not done it this way myself, but from my reading I understand that this allows you to return "virtual rows" in your result set which don't actually exist in the database.  Alex posted a link to an Oracle whitepaper on the Model clause on this forum a while back, which explains how that works.

                    • 7. Re: Trending Point in Time Data
                      Joe Mako



                      Here is a viz for how I interpreted your question:


                      Two lines, the count of tickets opened, and count of tickets closed, by month.


                      Since that wasn't very interesting, I ran with it a bit. If you download the workbook, from the "download" link in the bottom corner, there are a few other worksheets in it.


                      I think you will find this much more interesting:





                      Both tell some nice stories. For example in the waterfall, week 29 was a heavy week for closing tickets, and it has the added benefit of showing you how many tickets are open at that time (the running sum is the number open at the end of the week). In the heatmap, tickets are opened on Monday and Tuesday, and closed on Friday and Saturday (take a look at the pattern difference for weeks 19 and 20 - both lots of opens on Tues for both, and lots of closes on Sat for 19, and Thurs for 20).


                      For these three sheets that I directly linked to, I used the following Custom SQL:


                      SELECT [Sheet1$].[Assigned Group] AS [Assigned Group],
                         [Sheet1$].[Incident Number] AS [Incident Number],
                        [Sheet1$].[Priority] AS [Priority],
                        [Sheet1$].[Submitter Group] AS [Submitter Group],
                        [Sheet1$].[User Name] AS [User Name],
                        [Sheet1$].[Date Time Closed] AS [Event Date],
                        "Closed" AS [Event Type]
                      FROM [Sheet1$]
                      UNION ALL
                      SELECT [Sheet1$].[Assigned Group] AS [Assigned Group],
                        [Sheet1$].[Incident Number] AS [Incident Number],
                        [Sheet1$].[Priority] AS [Priority],
                        [Sheet1$].[Submitter Group] AS [Submitter Group],
                        [Sheet1$].[User Name] AS [User Name],
                        [Sheet1$].[Date Time Opened] AS [Event Date],
                        "Opened" AS [Event Type]
                      FROM [Sheet1$]


                      The key pairs of lines are:


                      [Sheet1$].[Date Time Closed] AS [Event Date],
                         "Closed" AS [Event Type]



                      [Sheet1$].[Date Time Opened] AS [Event Date],
                         "Opened" AS [Event Type]


                      What this SQL is doing is turning a structure like:

                      Ticket ID | Date Time Opened | Date Time Closed



                      Ticket ID | Event Date | Event Type


                      This will double your number of rows.


                      I also made a few calculated fields, let me know if you have any questions about how they work.


                      Attached is the original .xls file. In the packaged workbook I made, I connected to both as the single table and as the custom SQL so there are two data sources, and did a few things with both structures.


                      I spent more time creating this interesting non-real data set than making vizes. Of note is that in this data set, every ticket is closed, so in my waterfall, the difference goes back to zero. In real data, it will hover around the number of tickets open at that time.


                      I may come back to these and clean them up (details like cleaning up the tool-tips that I did not get to), but 3 hours on this is enough for me for the night.

                      • 8. Re: Trending Point in Time Data
                        . Derek



                        This is very helpful. Thanks a lot.


                        One other thing that I am trying to accomplish is to plot the number of tickets that were active at a point in time. Something like [date ticket opened] is less than the last day of each month AND the [date ticket closed] is greater than the last day of each month.


                        I am pretty sure I can calculate the counts on a per month basis, but how could I calculate it for all months at once?


                        Ultimately I would like to add a line for the number of active tickets at month end to your first chart.




                        Thanks again for all of your help.

                        • 9. Re: Trending Point in Time Data
                          Joe Mako



                          In most trouble ticket databases, only looking at one day's active ticket count in a month, does not represent the number of active tickets for the month.


                          I would recommend a view like this:



                          Where the line is each day's total, the shaded area is the min/max range, and the dark line is the month's average.

                          • 10. Re: Trending Point in Time Data
                            Joe Mako

                            Actually, I took a look at that, and in my previous comment, it is at any given time, each point in the line is an event (ticket opened or closed).





                            Each point in the line is EOD, active ticket count. If you compare this one with the previous one, you will see some peaks are less pronounced, this is because if 50 tickets were opened in the morning, and 25 of them closed before midnight that day, the 25 closed would essentially be counted as zero, and you would not see those 25 charted.


                            I prefer the by event charting, as you can see the real spikes, and see all the data.

                            • 11. Re: Trending Point in Time Data
                              . Derek

                              This is all great stuff. I can certainly think of cases where we would want to look at the data from both lenses.


                              How were you able to plot the EOD balance of active tickets? Was is a calculated field? I apologize for all the questions. I am a bit of a noob.


                              I want to represent three data points on a single graphic. The number of ticketed created during each month, the number completed each month, and the number that are still active as of the end of each month.


                              I should mention that the audience for this particular chart is executive management, which is why I am trying to get a snapshot of the month vs daily activity. While it is very useful to look at the data on a daily basis to help manage the day to day workload, that is not exactly what I am trying to accomplish.


                              If it is possible to display the data daily and then just collapse the timeline from days to months, then that would probably be the best of both worlds.



                              • 12. Re: Trending Point in Time Data
                                Joe Mako

                                Take a look at:



                                This worksheet would not be a final product to show to executives, but it does show the stories that I think you are looking to tell.


                                In this data set for example, March and Aug, show the same pattern, less tickets opened, more tickets closed. Looking at Apr and May, both had an increase in tickets, both opened and volume, but by the end of the month, May handled the increase better than Apr (-60 vs -11).


                                To create the viz, I used the custom SQL connection, that turns the original data from one row per ticket into one row per event (an event being an open or close), and use the following calculated fields:




                                IIF([Event Type]="Closed",1,0)




                                IIF([Event Type]="Opened",1,0)


                                Active Count


                                CASE [Event Type]
                                 WHEN "Opened"
                                THEN 1
                                WHEN "Closed"
                                THEN -1
                                ELSE 0

                                (with a Running Total Table Calculation)






                                and again Opened, but with a Percent Difference from Previous Table Calculation




                                LEFT(DATENAME('month',[Event Date]),3)

                                (to shorten the month name to three characters)


                                If I were to use this for display, I would add some drill down actions, setup a dashboard, so I could use a variety of coloring settings, and maybe set one of them to a line instead of a bar.


                                Maybe in a bit, I'll make a dashboard like that.


                                Anyone have any ideas on a dashboard for this data/questions?

                                • 13. Re: Trending Point in Time Data
                                  Joe Mako

                                  You may also want to consider reasons for why the numbers are changing the way that they do.


                                  Do you have numbers on staffing, customers, major events, and other factors that would influence these numbers to help explain why they changed as they did?

                                  • 14. Re: Trending Point in Time Data
                                    guest contributor

                                    This is helpful as I needed to do a YTD census count of cases. I used the instructions from joemako for open closed active count, etc. I have one small problem. If I have a month when there were no new cases and no cases closed then the running total table calcuation for that month shows up as 0 on the graph. any suggestions?

                                    1 2 Previous Next