2 Replies Latest reply on Mar 8, 2012 2:50 PM by Mike Balfour

    Display related field from the row containing MAX?

    Mike Balfour

      I've got a data set of automated actions (build, compile, deploy, and notify).  These actions run several times per day, and in real life (not in my sample data set) they occur in sequence - compile->build->deploy->notify.  I'm trying to build a graph that shows the max time for each sequence each day.

       

      I don't currently have any data connecting the sequenced data together, so for the moment I'm doing a "good enough" pass of just showing the max time of each step in the sequence on the graph.

       

      My question is that each of those max times is ultimately coming from a row of data that has an ID associated with it.  How can I display the ID from the row that contained the max time, as partitioned by Action?  (If more than one row has the max time, I'd be OK with tie-breaking by showing the min or max ID)

       

      I can't just use MAX(ID), since that isn't necessarily from the same row as the max time.

       

      I was thinking some type of table calculation should be able to get me what I need, but I haven't figured out how.  I tried this, but it just returns 0:

       

      // If this row has the max time...

      IF (LOOKUP(ATTR([Duration (Time)]),0) = LOOKUP(MAX([Duration (Time)]),LAST()))

      THEN

      // Then get the max ID from this row?

      LOOKUP(MAX([ID]),0)

      ELSE

      // Otherwise, use the value from the previous row, or 0 if this is the first row?

      PREVIOUS_VALUE(0)

      END

       

      In the attached workbook, the values I'm trying to have show up are the following:

      2/23 - Compile - ID 3

      2/23 - Build - ID 10

      2/23 - Deploy - ID 23

      2/23 - Notify - ID 33 (or 31)

      2/24 - Compile - ID 40

      2/24 - Build - ID 44

      2/24 - Deploy - ID 50

       

      Help?