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

    Get a datediff for each unique value

    Mathieu Laroche

      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.