Ranking in arrays 1

[johnwolf]

Hi to all

Using CR XI and Oracle.

I need to extract the 1st, 2nd, 3rd, 4th, 5th positions of an array of 5 variables Array = [{@a},{@b},{@c},{@d},{@e}]. (those variables are sales totals per salesmen).

To get the highest and lowest values of this array, it is easy, using maximum and minimum functions.

I wrote complicated formulas to extract 2nd, 3rd and 4th positions.

Her is the code I used to extract the second highest value

Select Maximum(Array)
Case {@a}:
(
NewArray:=[{@b},{@c},{@d},{@e}];
Maximum (NewArray)
)
Case {@b}:
(
NewArray:=[{@a},{@c},{@d},{@e}]
Maximum (NewArray)
)
...

The formula for the third highest is the same kind of formula, but heavier, and I am lucky we have only 5 sales people.

I saw there is a function nthlargest but as I understood, it can only apply to fields, not to summaries, and does not work with arrays.

Does anyone know how to workaround this formula to have the same result in an array without writing so much code when we have ten sales people?

Thanks in advance

 

[lbass]

You should be able to adapt faq767-4506 (by replacing the multi-valued parameter with your array) to sort the array in order. You could then reference the five highest or lowest values based on their ordinal position in the sorted array.

-LB

 

[johnwolf]

Thanks LB for your answer.

I am sure you are showing me the right direction but I just cannot figure it out

In the FAQ you mention, the multiple parameter list is already ordered and the values in the array belong to that list.

In my question, the values in the array do not belong to any list.
They are totals of conditional formulas located in the footer like

@A SALES
If {SALES.AGENT} ="A" then {SALES. AMOUNT} else 0

and so on..., so the values in the array can be any amount and cannot be known in advance.

I can find easily the array index for the lowest and the highest values but what I am looking for is a simple way to know the array index of the 2nd highest, 3rd highest, 4th highest...

I want to have this information in fixed positions of the footer of my report.

 

[lbass]

No, they aren't already ordered. The formula orders them. Then you can use the array index.

-LB

 

[johnwolf]

I am sorry I am figuring that the formula of the FAQ is placed at the detail level, where as mine as in the footer.

My mind will be fresher tomorrow morning. Thanks

 

[lbass]

I don't think it matters. What is the content of one of your formulas? e.g., {@a}?

-LB

 

[johnwolf]

{@a}
Sum(@A SALES)


{@A SALES}
If {SALES.AGENT} ="A" then {SALES. AMOUNT} else 0

 

[lbass]

Okay, here is a formula you can place in the report footer which will show the top five sales amounts from the array:

currencyvar array x :=[sum({@A SALES}),sum({@B SALES}),sum({@C SALES}),sum({@D SALES}),sum({@E SALES}),sum({@F SALES}), sum({@G SALES}];
currencyvar array y;
numbervar i;
stringvar n;
numbervar p;
numbervar j := ubound(x);
for i := 1 to j do(
redim preserve y[j];
y := maximum(x);
for p := 1 to j do(
redim preserve x[j];
if x[p] = y then
(
x[p] := 0;
exit for;
);
);
);
for i := 1 to 5 do( //show this number of top elements
n := n + totext(y,0)+"; "
);
left(n, len-2)

-LB

 

[johnwolf]

Many thanks, works perfectly as always.

It would not have occured to me to create another array and successively change to 0 the values of the first array.

Still learning thanks to you.