2 Replies Latest reply on Aug 21, 2013 8:29 AM by Tom Jones

    Determining the MAX value for each value in a given dimension

    Tom Jones

      I’m trying to calculate the maximum YTD daily case volume at each facility.  My data looks like:

      Facility

      Date

      Caseid

      A

      1/1/2013

      1

      A

      1/1/2013

      2

      A

      1/1/2013

      3

      B

      1/1/2013

      4

      C

      1/1/2013

      5

      A

      1/2/2013

      6

      B

      1/2/2013

      7

      B

      1/2/2013

      8

      C

      1/2/2013

      9

      A

      1/3/2013

      10

      B

      1/3/2013

      11

      C

      1/3/2013

      12

      C

      1/3/2013

      13

       

      I’d like to create a worksheet showing the max number of cases, along with the date (when the maximum cases occurred)  for each facility:

      Facility

      Date

      Max Case Count

      A

      1/1/2013

      3

      B

      1/2/2013

      2

      C

      1/3/2013

      2

       

      I’ve tried different approaches using Number of Records and COUNTD(Caseid), without success.  I’ve tried using a calculated field as a True/False filter like COUNTD(CaseID)=WINDOW_MAX(COUNTD(CaseID)) , but cannot get it to show the maximum value for each facility.

      Any recommendations?