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?