Getting Max Numbers Calc

[Zero Tolerence]

I have 7 fields, these fields can have numbers between 1 and 20. all numbers are two characters long (01, 02, 03..ext).

What I want, is only the biggest 5 numbers.

Is there a way to do this? The only way I can think of is to do something like this.

Let([

//Get the biggest number

Max = Max ( field1, field2, field3, field4, field5, field6, field7);

//Get the second biggest Number



Max2 = Case(

Max = field1; Max ( field2, field3, field4, field5, field6, field7);

Max = field2; Max ( field1, field3, field4, field5, field6, field7);

Max = field3; Max ( field1, field2, field4, field5, field6, field7);

Max = field4; Max ( field2, field3, field1, field5, field6, field7);

Max = field5; Max ( field2, field3, field4, field1, field6, field7);

Max = field6; Max ( field2, field3, field4, field5, field1, field7);

Max = field7; Max ( field2, field3, field4, field5, field6, field1));



//Get the Third Biggest Number

Max3 = Case(

Max = field1 and Max2 = field2; Max( field3, field4, field5, field6, field7);

Max = field1 and Max2 = field3; Max(field2, field4, field5, field6, field7);



and so on.

surely there is an easier way to do this.

By the time I get to the fifth number my code is going to be huge, anyone got any advice.

[Ender]

Sounds like a candidate for a CF. But what's your rule for dealing with ties? (does 05 05 05 count as one number or three?)

[Søren Dyhr]

Naugty ...naugty, unless it's keyfields we're talking about. But if it ain't take a look at this template:

http://www.fmforums.com/forum/attachment.php?attid/5451/

...from this thread:

http://www.fmforums.com/forum/showtopic.php?tid/171364/post/182834/hl//#182834

But if you're in the clear with good conscience, could this be used:

http://www.briandunning.com/cf/26

...and leading zero's is just what fits the bill, although they aren't the standard setup for autoID's....

--sd

[Zero Tolerence]

Ender:

05, 05, 05

would be three numbers, and if I coded the calc/cf the way I show above, it would actually still return me three numbers.

Thanks Soren, I'll play around with Brians Bubble function and see if it will do what I'm wanting.

[Søren Dyhr]

Don't give Brian the full credit, he's just hosting this function that Greg Lane from The Moyer Group, has made!

--sd

[comment]

There are several ways to do this. Five out of seven is not that many, but I think the "brute force" approach you have tried could be more efficient if one concentrated on finding the lowest two, and scratching them off the list.

Of course, the best way is to offload the values to a related file. Then you can use a value list, or GetNthRecord() to get any segment you need.