Ranking

[ScottWood]

Ive got a report, that shows product name, retail price, and , margin. the report is sorted by the retail price, what i need to do though is create some king of ranking on the margin field for example:

Product Retail Margin Rank
****** 100 30 3
****** 75 60 2
****** 50 10 4
****** 25 90 1

I need to keep the original sort order on the retail field, i just need a formula that will work out the rank of the maring field.

Thanks

 

[nagornyi]

Hopefully someone can show how to do this in loop, but if not just continue this formula up to the maximum number of products

if NthLargest (1,{Margin})={Margin} then 1 else
if NthLargest (2,{Margin})={Margin} then 2 else
if NthLargest (3,{Margin})={Margin} then 3 else
if NthLargest (4,{Margin})={Margin} then 4

 

[bdreed35]

I tried creating a loop with the NthLargest function inside, using the looping variable in the first parameter position. Crystal didn't except a variable in that position, even though it was numeric. It seems to require an actual number, 1 through 100, only. Has anyone else tried this with any success?

~Brian

 

[nagornyi]

I also tried the loop before suggesting the formula. It's strange a variable can't go there. The good thing is that the formula can be easily created even for hundreds of products, and that no need to know exactly how many of them.

 

[bdreed35]

Actually, you can't go any higher than 100, so your are limited to the Nth Largest up to 100.

~Brian

 

[ScottWood]

Cheers guys that worked a treat, i only needed the Nth largest up to 20 so it was fine.

Cheers