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:
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
But this is rather clumsy and is difficult to apply across several of these processes automatically.
How is your underlying data structured? Is there a dimension breaking apart the intervals? (guessing your [PID] field is doing this)
I'm not certain, but it seems like this should be possible with the right WINDOW functions and a table calc.
Could you post your workbook?