计算在给定时间工作的人数 [英] Counting the number of people working at a given time

问题描述

我有员工登录和退出时显示的数据.我将他们登录的持续时间称为就绪"时间，将他们注销的时间称为未就绪"时间.我想要做的是有一个连续的、渐变的时间线，显示有多少人处于就绪状态.

I have data the shows when employees log in and when they log out. I call the duration they are logged in "Ready" time and the time they are logged off as "Not Ready" time. What I want to do is have a continuous, gradient timeline that shows how many people are in the ready state.

关于数据的一些说明:

• 数据有开始时间和结束时间.每条记录对于员工和开始时间都是唯一的?
• 很遗憾，我无法发布此数据，因为它不属于我.

推荐答案

假设你的数据是这样的

employee    start_time  end_time
E1  7/7/2017  10:03:10 AM   7/7/2017  18:03:10 AM
E2  7/7/2017  10:10:10 AM   7/7/2017  18:03:10 AM
E3  7/7/2017  10:13:10 AM   7/7/2017  18:03:10 AM
E1  7/7/2017  11:03:10 AM   7/7/2017  19:03:10 AM
E2  7/7/2017  10:30:10 AM   7/7/2017  18:03:10 AM

在这种情况下，我会按照以下步骤操作:

In that case I would follow these steps:

• 检查 start_time 和end_time 列不是时间戳"格式，然后右键单击它并转到更改数据类型"以选择日期和"时间'
• 创建参数Select_Report_DateTime.在参数窗口中选择 Data type = 'Date &时间';显示格式 = '自动';允许值 = 'All'
• 右键单击此参数并选择显示参数控件"
• 将计算字段 default_date_time 创建为 #1/1/1900 12:00:00 AM#.如果它不是时间戳"格式，则右键单击它并转到更改数据类型"以选择日期和".时间'
• 创建计算字段 Report_DateTime_formatted 为

• Check if start_time & end_time columns are not in 'timestamp' format then right click it and go to 'Change data type' to select 'Date & Time'
• Create a parameter Select_Report_DateTime. In parameter window select Data type = 'Date & Time'; Display format = 'Automatic'; Allowable values = 'All'
• Right click this parameter and select 'Show parameter control'
• Create calculated field default_date_time as #1/1/1900 12:00:00 AM#. If it's not in 'timestamp' format then right click it and go to 'Change data type' to select 'Date & Time'
• Create calculated field Report_DateTime_formatted as

IF [Select_Report_DateTime] == [default_date_time] then now()
ELSE [Select_Report_DateTime]
END

<小时>

• 创建一个计算字段Users(inReadyState) as

IF (([Report_DateTime_formatted] >= [start_time]) & 
    ([Report_DateTime_formatted] < [end_time])) THEN "Yes"
ELSE "No"
END

<小时>

• 现在将 Users(inReadyState) 拖到 'Columns' 中，将 CNT[employee] 拖到 'Rows' 中，并将 'Marks' 拖到 Bar 中.
• 您可以很好地使用此模型来根据您的特定要求获得 o/p.

---

• Now drag Users(inReadyState) in 'Columns', CNT[employee] in 'Rows' and 'Marks' as Bar.
• You can very well play around with this model to get the o/p as per your specific requirement.

如果有帮助，请不要忘记告诉我们:)

这篇关于计算在给定时间工作的人数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！