Trying to calculate a total of minutes that an employee was clocked to labor. The caveat is the way things are clocked here I can't just use a min/max/next/previous because it isn't sequential.
试着计算一名员工被判劳动的总时间。需要注意的是，这里我不能只使用min/max/next/previous因为它不是顺序的。
Easier to explain with an example. Here's a simple bit of data that is narrowed down to just one employee for one day. I removed the order/operation but realize every record below is a specific order and operation on that order. The records don't effect each other, so any record could be the latest.
用一个例子更容易解释。这里有一个简单的数据，它被缩小到只有一个员工一天。我删除了订单/操作，但意识到下面的每条记录都是该订单的特定订单和操作。这些记录不会相互影响，所以任何记录都可能是最新的。
STARTDATETIME STOPDATETIME
8/21/2017 6:57:00 AM 8/21/2017 10:45:00 AM
8/21/2017 6:57:00 AM 8/21/2017 10:45:00 AM
8/21/2017 6:57:00 AM 8/21/2017 10:45:00 AM
8/21/2017 6:57:00 AM 8/21/2017 10:46:00 AM
8/21/2017 6:57:00 AM 8/21/2017 11:28:00 AM
8/21/2017 6:58:00 AM 8/21/2017 11:28:00 AM
8/21/2017 11:58:00 AM 8/21/2017 12:05:00 PM
If I manually calculate this I can see the person worked from 6:57 - 11:28 without stop (271 minutes)
如果我手动计算的话，我可以看到这个人从6:57到11:28不停地工作(271分钟)
And then they also worked from 11:58-12:05 (7 minutes)
然后他们也从11:58-12:05(7分钟)开始工作
So their total for the day was 278 minutes. I'm having trouble figuring out how to calculate this though.
这一天总共是278分钟。我不知道怎么计算这个。
The other data I do have readily available is their clock in and out time for the day, so if it is easier to count the minutes they weren't clocked to something we can get that as well. (Clock in and out in this example was 5:49 and 12:30 for a total of 401 minutes)
我手头上的另一项数据是他们一天的进进出出的时间，所以如果更容易数出他们没有记录的时间，我们也能得到。(在本例中，时钟进进出出的时间为5:49和12:30，总共为401分钟)
Would love to hear any ideas. I'm going to be displaying charts/graphs using this data in Tableau and for the backend I'm using a HANA database.
我很想听听你们的意见。我将使用表中的这些数据显示图表/图表，对于后端，我将使用HANA数据库。
1 个解决方案
#1
2
For this type of question, I would definitely recommend transforming and prepping the data outside of Tableau in python for example. Tableau is very good at visualizing data points in Time series, however, your data is irregular and not evenly spaced, and although Table and Level of Detail calculations can traverse across rows, your question is a medium level one, that would be difficult to answer with the tool you're attempting to.
对于这种类型的问题，我肯定会推荐使用python来转换和准备表外的数据。表是很好的可视化数据的时间序列,然而,你的数据是不规则和不均匀间隔的,虽然表和详细计算可以遍历整个行,你的问题是一个中等水平,很难回答你试图与工具。
If you can't perform an intermediate transformation, and depending on the number or records and time span of your data, you can brute force a regular time series by performing a Cartesian join (or perhaps blend) with a table of values for every minute and in the date range you're looking at. Then filter (or create a binary field) out all values for which the open is >= the join data, and close date is < the blend date. This should provide you with a record and date for every minute that they worked, making it a lot easier to perform calculations on the data. In your case, you can then just SUM the number records to get the number of minutes worked.
如果你不能做一个中间变换,根据数量或记录和时间跨度的数据,你可以蛮力普通时间序列通过执行笛卡尔连接(或混合),表值每一分钟和你看日期范围。然后过滤(或创建一个二进制字段)出打开的值>=连接数据，关闭日期为 <混合日期。这将为您提供他们工作的每一分钟的记录和日期，使对数据进行计算变得更加容易。在你的例子中，你只需要把数字记录相加就可以得到工作的分钟数。< p>
This thread has a lot of good information on wrangling time series data depending on your goal. It also includes more detailed information on how to perform cross joins in Tableau https://community.tableau.com/thread/151387
这个线程有很多关于时间序列数据的好信息，这取决于你的目标。它还包含关于如何执行表中https://community.tableau.com/thread/151387中的交叉连接的详细信息
#1
2
For this type of question, I would definitely recommend transforming and prepping the data outside of Tableau in python for example. Tableau is very good at visualizing data points in Time series, however, your data is irregular and not evenly spaced, and although Table and Level of Detail calculations can traverse across rows, your question is a medium level one, that would be difficult to answer with the tool you're attempting to.
对于这种类型的问题，我肯定会推荐使用python来转换和准备表外的数据。表是很好的可视化数据的时间序列,然而,你的数据是不规则和不均匀间隔的,虽然表和详细计算可以遍历整个行,你的问题是一个中等水平,很难回答你试图与工具。
If you can't perform an intermediate transformation, and depending on the number or records and time span of your data, you can brute force a regular time series by performing a Cartesian join (or perhaps blend) with a table of values for every minute and in the date range you're looking at. Then filter (or create a binary field) out all values for which the open is >= the join data, and close date is < the blend date. This should provide you with a record and date for every minute that they worked, making it a lot easier to perform calculations on the data. In your case, you can then just SUM the number records to get the number of minutes worked.
如果你不能做一个中间变换,根据数量或记录和时间跨度的数据,你可以蛮力普通时间序列通过执行笛卡尔连接(或混合),表值每一分钟和你看日期范围。然后过滤(或创建一个二进制字段)出打开的值>=连接数据，关闭日期为 <混合日期。这将为您提供他们工作的每一分钟的记录和日期，使对数据进行计算变得更加容易。在你的例子中，你只需要把数字记录相加就可以得到工作的分钟数。< p>
This thread has a lot of good information on wrangling time series data depending on your goal. It also includes more detailed information on how to perform cross joins in Tableau https://community.tableau.com/thread/151387
这个线程有很多关于时间序列数据的好信息，这取决于你的目标。它还包含关于如何执行表中https://community.tableau.com/thread/151387中的交叉连接的详细信息