topic Inverse Ranking in QlikView

Inverse Ranking

mike_garcia — Wed, 21 Apr 2010 19:51:04 GMT

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!

Inverse Ranking

sathishkumar_go — Wed, 21 Apr 2010 20:01:18 GMT

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

Inverse Ranking

mike_garcia — Wed, 21 Apr 2010 20:07:48 GMT

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.

Inverse Ranking

Wed, 21 Apr 2010 20:14:32 GMT

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

Inverse Ranking

mike_garcia — Thu, 22 Apr 2010 02:03:06 GMT

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

Mike

Re: Inverse Ranking

stuwannop — Wed, 07 Aug 2013 12:31:32 GMT

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