Rank Help

[Anonymous]

Afternoon folks,

I have the below table in my Qlikview app, and I am trying to add a Rank Column to the far left of it. How do I write a rank expression for this table that ranks the data by the success % (high to low), then by # of docs (again high to low) and then by Site Code (A-Z) so that I achieve the visible ranking in the ranking column below:

Rank	Site Code	Site Name	# of Docs	Failure	Success	Other	Total	Success %
1	B	B	1058	0	1057	1	1058	100.00%
2	J	J	136	0	4	0	4	100.00%
3	C	C	940	26	883	3	912	97.14%
4	A	A	7097	326	5962	25	6313	94.82%
5	G	G	371	26	142	3	171	84.52%
6	F	F	381	37	201	8	246	84.45%
7	E	E	578	50	188	0	238	78.99%
8	D	D	743	51	189	3	243	78.75%
9	H	H	179	0	0	0	0	-
10	I	I	172	00	0	0	0	-

I have googled how to do this but can't actually find the method I'm looking for.

Thanks,

Gareth

[sunny_talwar]

Can you share the output you are seeing?

[Anonymous]

It's ok, I solved that particular issue by slightly amending my expression.

Rank(TOTAL

RangeSum(

Count({<[FTMR_P2P_KPI]={"Success"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number])

/

(Count({<[FTMR_P2P_KPI]={"Failure","Success"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number])),

Count({<[FTMR_P2P_KPI]={"Failure","Success"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number])/1E8,

-Rank(TOTAL [BU Cat 10])/1E10))

However, if you look at the attached you will see ranks 288 and 292 should actually be ranks 282 and 283 respectively.

Many thanks,

Gareth

[sunny_talwar · Accepted solution]

Not sure Gareth.... very difficult to say without having a look my friend

You can check the expression we are ranking and try to adjust the weights

RangeSum(

([Success]

/

([Success] + [Failure]))*100000,

Total/1E5,

-Rank(TOTAL [Site Code]/1E10))

Make sure you see this with a lot of 0s after decimal point. Something like this