时间点计算

发布于 2025-02-01 21:29:50 字数 603 浏览 2 评论 0原文

我有一个挑战，不确定如何解决。我有一些类似的呼叫中心数据＆nbsp;

Incident number    Received date    Closed Date
-----------------------------------------------
  111               01/01/2020      01/06/2020
  222               01/04/2020      01/09/2020
  333               01/05/2020      01/10/2020
  444               01/07/2020

我想做的是我的时间报告＆nbsp; 2020年的每个月，公开事件的平均年龄是多少。＆nbsp;因此，例如在2020个月＆nbsp;一个人数只有一个，因为在2020年在2020 01中筹集了一个事件，并且在2020年01中仍开放。到5个月，计数将为3个，因为尚未关闭事件，但是到第6个月，计数将会为2，因为我们现在在第6个月关闭了索赔。＆nbsp;剩下的事件将在创建日期之后的每个月计算，直到关闭日期。它必须脚本脚本，但不确定如何解决这个问题。有什么想法吗？

可用工具：Qlikview，SAS，Excel

原文

I have a challenge that and unsure how to tackle it. I have some call centre data like this

Incident number    Received date    Closed Date
-----------------------------------------------
  111               01/01/2020      01/06/2020
  222               01/04/2020      01/09/2020
  333               01/05/2020      01/10/2020
  444               01/07/2020

What I want to do i point in time reporting for each month in 2020 what was the average age of open incidents. So for example in 2020 month one the count will be only one as there has been one incident raised in 2020 01 and it was still open in 2020 01. By month 5 the count will be 3 as none of the incidents have closed yet, however by month 6 the count will be 2 as we now have a claim closed in month 6. The incidents that remain open will be counted for every month after the created date until the closed date. It would have to be scripted but not sure how to tackle this. Any ideas?

Tools available: QlikView, SAS, Excel

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

油焖大侠 2025-02-08 21:29:50

计算事件的公式：

=COUNTIFS($B$2:$B$5;"<="&EDATE(E3;1)-1)-COUNTIFS($C$2:$C$5;"<="&EDATE(E3;1)-1)

我们使用Edate恰好比当前的一个月，然后减去一个月，因此我们始终获得的最后一个月的，即使如果有28、29、30 O 31天。

使用Countif，您可以计算在本月最后一天之前开放的总事件，减去当月最后一天之前的总截止事件。

本月列的价值必须是每个月的第一天，但是由于格式，您可以将细胞格式化为看起来像一个月（但是价值永远是一天！！！）它看起来更专业：

aplying格式mmm yy选择使它看起来更好（只是我的意见）

Formula to count incidents:

=COUNTIFS($B$2:$B$5;"<="&EDATE(E3;1)-1)-COUNTIFS($C$2:$C$5;"<="&EDATE(E3;1)-1)

We use EDATE to get exactly 1 month ahead of current one, and minus one, so we get always the last day of the current month, even if it has 28, 29, 30 o 31 days.

With COUNTIF you count total incidents opened before last day of current month minus total closed incidents before last day of current month.

The value of the Month columns must be first day of each month but thanks to format, you can format the cells to look like just the month (but the value will be always a day!!!) so it looks more professional: