1

I have an incident queue, consisting of a record number-string, the open time - datetime, and a close time-datetime. The records go back a year or so. What I am trying to get is a line graph displaying the queue volume as it was at 8PM each day. So if a ticket was opened before 8PM on that day or anytime on a previous day, but not closed as of 8, it should be contained in the population.

I tried the below, but this won't work because it doesn't really take into account multiple days.

If DATEPART('hour',[CloseTimeActual])>18 AND DATEPART('minute',[CloseTimeActual])>=0 AND DATEPART('hour',[OpenTimeActual])<=18 THEN 1
ELSE 0
END

Has anyone dealt with this problem before? I am using Tableau 8.2, cannot use 9 yet due to company license so please only propose 8.2 solutions. Thanks in advance.

Travis
  • 401
  • 1
  • 5
  • 21

1 Answers1

3

For tracking history of state changes, the easiest approach is to reshape your data so each row represents a change in an incident state. So there would be a row representing the creation of each incident, and a row representing each other state change, say assignment, resolution, cancellation etc. You probably want columns to represent an incident number, date of the state change and type of state change.

Then you can write a calculated field that returns +1, -1 or 0 to to express how the state change effects the number of currently open incidents. Then you use a running total to see the total number open at a given time.

You may need to show missing date values or add padding if state changes are rare. For other analytical questions, structuring your data with one record per incident may be more convenient. To avoid duplication, you might want to use database views or custom SQL with UNION ALL clauses to allow both views of the same underlying database tables.

It's always a good idea to be able to fill in the blank for "Each record in my dataset represents exactly one _________"

Tableau 9 has some reshaping capability in the data connection pane, or you can preprocess the data or create a view in the database to reshape it. Alternatively, you can specify a Union in Tableau with some calculated fields (or similarly custom SQL with a UNION ALL clause). Here is a brief illustration:

select open_date as Date,
       "OPEN" as Action,
       1 as Queue_Change,
       <other columns if desired>
from incidents
UNION ALL
select close_date as Date,
       "CLOSE" as Action,
       -1 as Queue_Change,
       <other columns if desired>
from incidents
where close_date is not null

Now you can use a running sum for SUM(Queue_Change) to see the number of open incidents over time. If you have other columns like priority, department, type etc, you can filter and group as usual in Tableau. This data source can be in addition to your previous one. You don't have ta have a single view of the data for every worksheet in your workbook. Sometimes you want a few different connections to the same data at different levels of detail or for perspectives.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • For the purposes of what we are trying to track, the two states are OPEN and CLOSED. I have an Open Date and a Closed Date, so the first portion of your response is not applicable. I also am able to get the number that would have been open at any given point in time. As explained, what I cannot do, is figure out the code for a calculated field that would return a value for the number of open tickets on each day, facilitating a line graph of the daily incident queue volume at 6PM. – Travis May 13 '15 at 17:58
  • @ Travis, The approach I described works in your case. I've done it many times. But you need to reshape your data to have a single date column, and a row for each state change (open or close). You'll also need a column to distinguish opens from closes. Tableau 9 helps with reshaping, or you can preprocess or you can use custom SQL with a UNION ALL clause. I'll edit the answer to illustrate briefly. – Alex Blakemore May 13 '15 at 19:10
  • @ Alex I can't alter the source data at all. I would love to be able to, SQL is much easier to work with than Tableau. – Travis May 13 '15 at 19:22
  • @Travis That's the beauty of custom SQL. You don't have to alter the data source. You just change the query that Tableau sends to the datasource. Read about custom SQL in the Tableau docs. If your data source is Excel then you will need to use the legacy driver to use custom SQL. Tableau is easier than hand crafting SQL most of the time once you learn it well, but even better if you know both – Alex Blakemore May 14 '15 at 06:16
  • Thanks Alex I will check that out. The source is a csv extract that produced daily by a cron job. – Travis May 15 '15 at 12:59
  • ok near as I can tell it can't be done with two different data source types, one comes from a csv the other from a SharePoint list. I would love to be wrong on this. – Travis May 15 '15 at 13:08
  • Custom SQL works with a single data source. Data blending is the feature to combine information from multiple sources. If you really have incident details with the same fields, but in 2 separate data sources, you could use custom SQL on each to shape the data as described, and then append both. But it is getting complicated. How are you talking to SharePoint? and why excel also? To go much further, post some details – Alex Blakemore May 15 '15 at 18:56
  • 1
    @Travis If your source is CSV (it doesn't matter that the CSV comes from SharePoint originally), then you can use custom SQL (at least on Windows). The trick is you have to use the legacy Excel/CSV driver to connect to the CSV file because the newer one doesn't support custom SQL.The legacy connector can be found by selecting a new text connection and then instead of selecting "Open", select the drop down next to the Open button and select "Open with Legacy Connection". – Alex Blakemore May 15 '15 at 20:32