12 Replies Latest reply: Mar 30, 2015 10:18 PM by Michael Costner RSS

    Select Max Value from a list of values

    Michael Costner

      Hi everyone,

       

      I have a table with two dimensions (date and code) and one expression (value).

       

      There are three distinct codes, RFN, NP, and PJ. Each code has a value. The value is a count of the number of times a code occurs on a specific date. I would like to select the max value from each code, and repeat it for the remaining codes on each date.

       

      For example on 04-MAR-15 the max value is 5, so I would repeat 5 for each codes. On 06-MAR-15 the max value is 4 so I would like to repeat 4 for each code.

       

      The table on left is what I currently have. The table on the right is what I'm trying to achieve (these are hard coded currently).

       

      I've tried using the statement:

       

      =if(CODE = 'NP' or CODE = 'PJ', count(distinct {$<prodtime_CODE ={"*"}-{'NP', 'PJ'}>} VALUE), count(distinct {$<CODE ={"*"}-{'NP', 'PJ'}>} VALUE))


      but it removes the NP and PJ codes from the table.


       

      Any ideas how I can accomplish this?

       

      Thanks!

       

      Michael