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.
我有一个事件队列,包括记录号字符串,开放时间 - 日期时间和关闭时间 - 日期时间。记录可追溯到一年左右。我想要得到的是一个线图,显示每天晚上8点的队列量。因此,如果在当天晚上8点之前或前一天的任何时间打开机票,但在8点之前没有关闭,则应该包含在人口中。
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.
有没有人以前处理过这个问题?我使用的是Tableau 8.2,由于公司许可证尚未使用9,所以请仅提出8.2解决方案。提前致谢。
1 个解决方案
#1
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.
然后,您可以编写一个返回+1,-1或0的计算字段,以表示状态更改如何影响当前打开的事件数。然后使用运行总计来查看在给定时间打开的总数。
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.
如果状态更改很少,您可能需要显示缺少的日期值或添加填充。对于其他分析问题,使用每个事件一条记录来构建数据可能更方便。为避免重复,您可能希望使用数据库视图或带有UNION ALL子句的自定义SQL来允许同一底层数据库表的两个视图。
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:
Tableau 9在数据连接窗格中具有一些重塑功能,或者您可以预处理数据或在数据库中创建视图以重新整形。或者,您可以在Tableau中指定具有一些计算字段的Union(或类似的带有UNION ALL子句的自定义SQL)。这是一个简短的说明:
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.
现在,您可以使用SUM(Queue_Change)的运行总和来查看随时间推移的打开事件的数量。如果您有其他列,如优先级,部门,类型等,您可以像往常一样在Tableau中过滤和分组。此数据源可以是您之前的数据源。您没有工作簿中每个工作表的单一数据视图。有时,您需要在不同的详细级别或透视图中使用相同数据的几个不同连接。
#1
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.
然后,您可以编写一个返回+1,-1或0的计算字段,以表示状态更改如何影响当前打开的事件数。然后使用运行总计来查看在给定时间打开的总数。
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.
如果状态更改很少,您可能需要显示缺少的日期值或添加填充。对于其他分析问题,使用每个事件一条记录来构建数据可能更方便。为避免重复,您可能希望使用数据库视图或带有UNION ALL子句的自定义SQL来允许同一底层数据库表的两个视图。
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:
Tableau 9在数据连接窗格中具有一些重塑功能,或者您可以预处理数据或在数据库中创建视图以重新整形。或者,您可以在Tableau中指定具有一些计算字段的Union(或类似的带有UNION ALL子句的自定义SQL)。这是一个简短的说明:
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.
现在,您可以使用SUM(Queue_Change)的运行总和来查看随时间推移的打开事件的数量。如果您有其他列,如优先级,部门,类型等,您可以像往常一样在Tableau中过滤和分组。此数据源可以是您之前的数据源。您没有工作簿中每个工作表的单一数据视图。有时,您需要在不同的详细级别或透视图中使用相同数据的几个不同连接。