Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi bhpage,

 

Thanks for your response.

 

If I understand correctly the formula compares the hourcodekey of the current rowcontext with the hourcodekey of the preceeding rowcontext. If the hourcodekey in the current rowcontext is 160  and the hourcodekey in the preceeding rowcontext is not 160 than that is the day the employee called in sick for the first time.

 

Unfortunately the formula doesn't work with my real-life version of the data. The only thing different is that there are multiple employees in the real table who all have time registration data on the same dates. The error message reads "A table of multiple values was supplied where a single value was expected".

 

Can you solve this problem as well?

 

Rg. Erwin

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Can you provide an example of a customer like this?