9 Replies Latest reply on Jan 2, 2014 10:44 AM by Matt Lutton

    Attempting to use only MAX([Date]) rows in calculation

    Caleb Smith

      Hi All,

       

      I have a table of state transition data that logs the Project ID, Current Status, State, and Date Entered State. I'm trying to find the number of days a given project has been sitting in its current state since the most recent transition to that state.

       

      For example, Project1 may have entered the state of "Ready to Submit" five different times. I only want to count the number of days since its most recent [Date Entered State]. I was thinking that the MAX() function would be perfect for this but so far haven't been able to get it to work the way I want.

       

      My first attempt was the following:

       

      IF [State] = [Current Status]

      THEN DATEDIFF('day',MAX([Date Entered State]),TODAY())

      END

       

      The problem here is that MAX([Date Entered State]) returns an aggregate and so I can't use it.

       

      My second attempt was slightly more sophisticated. I attempted to use the secondary characteristic of the MAX() function to get around the aggregate problem.

       

      IF [State] = [Current Status]

      AND DATE(MAX(DATETRUNC('week',[Date Entered State]),DATETRUNC('month',[Date Entered State]))) = DATE([Date Entered State])

      THEN DATEDIFF('day',[Date Entered State],TODAY())

      END

       

      But this returns too few results.

       

      I've attached a sample workbook with some sample data and the calculations I've tried so far. Any help would be appreciated as I imagine I'll need to return to this MAX() issue frequently in my future work.