Inverse Ranking

[mike_garcia]

Hello Everyone,

I have the requirement to rank certain values in a straight table in reverse order. That means if I have, for example, Sales values from 1 to 10, the function rank(sum(Sales),3,1) will retrun 1 for the greatest sales and 10 for the lower sales. I want it to return 1 for the LOWEST Value and 10 for the greatest Value.

I hope somebody can help me out.

Thanks!

[sathishkumar_go]

Hi Mike,

if u want reverse order thats what u r using rank?

then better try Strainght table ->Properties->sort

i think it ll help for u

-Sathish

[mike_garcia]

Thanks, but thats not what Im looking for. I need to assign a ranking to every row in the straight table, no matter the sort order.

[#4 · Accepted solution]

Hi

I suppose your sales are positive, so just use : rank(-sum(Sales))

jj

[mike_garcia]

Thank you!!!! That was it... I appreciate your help.

Mike

[stuwannop]

This is probably just a syntax thing but what if the formula was slightly more complex: i.e.

=

rank((count(DISTINCT{$<OPPStatus ={'Open'},OPPSalesStage = {'5 - Confident','6 - Committed'},Dim_SPECFlag= {'1'}>}opportunityid))/

(count(DISTINCT{$<OPPStatus ={'Open'},Dim_SPECFlag = {'1'}>}opportunityid)))

No need to worry too much about the set analysis but I want the same result as the original poster but I'm using count and dividing two numbers together - any ideas?

Thanks

Stu