1 Reply Latest reply on Sep 26, 2016 2:54 PM by Alex Xu

# Get a datediff for each unique value

Hi,

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.

• ###### 1. Re: Get a datediff for each unique value

you can directly countd the date and use LOD to include or exclude employees.

I attached the tableau 10 version. (also some screenshots below in case you are not using tableau 10)

1 of 1 people found this helpful