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

[Clever_Anjos]

Try

MAX(TOTAL <Date> value)

[Anonymous]

That removes everything from the table.

[Clever_Anjos]

Did I typed your field name correctly? value <> Value

[Anonymous]

Yes, I typed in the field name correctly

[JonnyPoole]

max( total <Code> aggr( sum(Value),Code,Date) )

[Clever_Anjos]

Maybe

MAX(TOTAL <Date> AGGR( SUM (Value),Code,Date) )

[Anonymous]

That is closer, but still not quite right.

It applies the max value between NP and PJ to the NP and PJ codes, but it does not apply the max value of RFN.

[JonnyPoole]

interesting.  i got different results when i typed in your data.

You may have hidden (but important) details in your app that you may need to share...

[awhitfield]

HI MIchael,

this may seem a daft question, but why do you want to do this, what are you trying to achieve eventually?

Andy