This content has been marked as final. Show 1 reply
I'm stuck with a calculation I would like to do. I couldn't find any examples of this in the other demo projects or any inspiration by reading all functions available in Tableau.
What I would like to do is to come up with the total number of days each employee has worked so I can use that value to calculate overtime. I'm pretty much trying to do a DateDiff ('days', min(date), max(date).
But I don't want the min and max of all my rows because some employees have started later (so minimum is incorrect) or finished earlier (so maximum is not correct).
My data is like this:
Employee / Date / Hours
Bob / 2016-04-04 / 8
Joe / 2016-04-03 / 6
Bob / 2016-04-05 / 9
This was my attempt... but it doesn't work as explained earlier (the min and max is coming from all employees instead of each one of them)
[Hours] / ((DATEDIFF('day', Min([Date]), Max([Date]), 'sunday')+1)*(40/7)*COUNTD([Employee])
From the data put in attachment, I would like to arrive to:
ALBR working period is 25 days
ZAHA working period is 26 days
MARN working period is 18 days
Total is 69 days
SumOfAllHours / (69 days * 40h/7days)
432/394.29 = 109%
Meaning 9% of overtime was done
Thank you for your help. I know it might not be an easy question and I hope I'm clear with description.