Select Max Value from a list of values

[Anonymous]

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

[Anonymous]

Thanks for the help so far everybody.

Here is a slightly more long-winded version of what I'm trying to accomplish.

I have a multiple employees who can work three types of jobs-- RFN, NP, and PJ. I have a table that includes a date, employee, job type (Code), and hours worked.

What I would like to show is the average amount of hours worked in each code per employee per date.

For example, I had 5 separate employees work on 02-MAR-15. I want to take (sum(RFN hours) / 5), (sum(NP hours) / 5), and (sum(PJ hours) / 5).

Currently, I am only able to divide by the number of employees that have hours worked for the specific job type. So, on 02-MAR-15 for PJ hours I want 1.5 / 5 = 0.3. What I am getting is 1.5 / 1 = 1.5. And for NP hours I want 16.54 / 5 = 3.31, but it returns 16.54 / 4 = 4.135.

Ultimately I want a table with three columns: date, code, and hours. Each date would have a separate row for RFN, PJ, and NP. The hours worked would represent the total amount of time spent working in each code divided by the total number of operators who worked that day.

Thanks for all the help so far. Let me know if there is anything other information that would be pertinent. This in all likelihood much easier than I am trying to make it.

[JonnyPoole · Accepted solution]

My answer is as follows and attached

sum( total <Date,Code> Hours)

/

count(DISTINCT total <Date> Inits)

[Anonymous]

Jonathan-- that works perfectly.

Thanks!

Michael