Determination of the absence rate

joslaz

Board Regular
Joined
May 24, 2018
Messages
76
Hello everybody,


I am currently trying to determine from the data model, how high the absence rate for the individual weeks or months.
So far, I've just added the factor to the pivot calendar, whether it's a workday.


The table with the presence or absence of the employees is structured as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Shift[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]Anton[/TD]
[TD]Early[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]02.01.2019[/TD]
[TD]Anton[/TD]
[TD]Early[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]



Employees on holiday are in the state with U marked for the respective day.


I. How would you determine the absence rate using a measure?
II. How would you determine the absence rate, if there are still employees who work on the regular weekend but have Monday and Tuesday free?





I am grateful for every tip / approach!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hey Matt, thanks for your approach!

I have worked out an approach like that:

= (
COUNTROWS (
FILTER (
'Process Staffing Total';
'Process Staffing Total'[Status]
IN {
"U";
"P"
}
)
)
)
/ CALCULATE (
SUM ( 'Calendar'[Workdayfactor] )
)

So this functions sums all rows, that contains U or P (addional day off) and dived the sum by the workday factor sum.
This approach just shows empolees which are absence. But I want also the employees, that are present.
How can I include also the 0% absence rate for employees, that are present?
 
Upvote 0
With the following function the pivot contains also the blanks.
Code:
[COLOR=#333333][FONT=Tahoma][I]=( ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]COUNTROWS ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]FILTER ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]'Process Staffing Total';[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]'Process Staffing Total'[Status][/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]IN {[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]"U";[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]"P"[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]}[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]+0)/ CALCULATE ([/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I]SUM ( 'Calendar'[Workdayfactor] )[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=Tahoma][I])[/I][/FONT][/COLOR]

If I decrease the granularity (no name) the Average just sums up. There is no avergage - just a huge number.
How can I adapt the average?
So the pivot doesnt show the absence rate per employee > the average of all employes should be shown.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top