1 Reply Latest reply on Oct 9, 2012 1:28 PM by Mark Holtz

    Datediff Function with Variable Start Date

      I am trying to compare productivity rates of short processes (less than a week) that happen an irregular intervals.  I would like to use the formula:

       

      DATEDIFF('day',MIN([ShiftDate]),[ShiftDate]))+1

             

      But the DATEDIFF function will not allow the use of an aggregated function (MIN).  I have tried to convert MIN([ShiftDate]) to a string and then convert back to a date in an attempt to "trick" tableau into forgetting it is an aggregated value without success. 

             

      For the meantime I have calculated it this way:

             

      if [PID] = "10" then (DATEDIFF('day',#November 10, 2011#,[ShiftDate]))+1

      elseif [PID] = "11" then DATEDIFF('day',#September 10, 2012#,[ShiftDate])+1

      elseif [PID] = "12" then DATEDIFF('day',#October 10, 2012#,[ShiftDate])+1

      elseif [PID] = "13" then DATEDIFF('day',#October 18, 2012#,[ShiftDate])+1

      end

             

      But this is rather clumsy and is difficult to apply across several of these processes automatically.

           

      Any Ideas?

          

      -Stu