Forum Discussion
Calculate sick report frequency
Hi all, I need to show how many times an employee called in sick during a time period. I have a table of time registration data to do this. Below you will find a simplified example of this table. ...
Hi,
I believe I've written a calc that does what you're looking for:
Sick Occurence =
IF (
LOOKUPVALUE(
Sheet1[Hourcodekey],
Sheet1[Date],
CALCULATE (
MAX ( Sheet1[Date] ),
FILTER(
Sheet1,
Sheet1[Date] < EARLIER(Sheet1[Date]) &&
Sheet1[EmployeeKey] = EARLIER(Sheet1[EmployeeKey])
)
)
) <> 160
&& Sheet1[Hourcodekey] = 160
, 1
)
I tried to set this up to work across customers as well. Here's the result in the sample data:
Let me know if this works for you.
Ben
Thanks a lot! I get the following error when I try to apply this calculation in a similar dataset with multiple similar dates:
A table of multiple values was supplied where a single value was expected.
Dataset example:
| Date | Start time | Hourcodekey | employeekey | Sick occurence should be: |
| 1-6-2020 | 09:00 | 103 | 1201 | |
| 2-6-2020 | 09:00 | 103 | 1201 | |
| 3-6-2020 | 09:00 | 160 | 1205 | 1 |
| 4-6-2020 | 09:00 | 160 | 1205 | |
| 4-6-2020 | 09:00 | 103 | 1201 | |
| 4-6-2020 | 15:00 | 103 | 1201 | |
| 5-6-2020 | 09:00 | 103 | 1201 | |
| 6-6-2020 | 09:00 | 160 | 1201 | 1 |
| 6-6-2020 | 09:00 | 160 | 1205 | 1 |
| 7-6-2020 | 09:00 | 103 | 1201 | |
| 8-6-2020 | 09:00 | 103 | 1201 | |
| 8-6-2020 | 09:00 | 160 | 1205 | 1 |
| 8-6-2020 | 09:00 | 160 | 1205 | |
| 9-6-2020 | 09:00 | 103 | 1201 | |
| 10-6-2020 | 09:00 | 103 | 1201 | |
| 11-6-2020 | 09:00 | 103 | 1201 | |
| 12-6-2020 | 09:00 | 160 | 1201 | 1 |
| 12-6-2020 | 15:00 | 160 | 1201 | |
| 12-6-2020 | 09:00 | 103 | 1205 | |
| 14-6-2020 | 09:00 | 103 | 1201 | |
| 15-6-2020 | 09:00 | 103 | 1201 |
Hope someone can help us out