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

[Clever_Anjos]

Wich expression is "Success %" ?

[Anonymous]

Success % is "Success / (Success + Failure)"

Thanks

[sunny_talwar]

Would you be able to share a qvw sample to look at this?

[Clever_Anjos]

and "Success" and "Failure"?

[Anonymous]

Hi,

QVW Sample attached.

Thanks,

Gareth

[sunny_talwar]

May be this

Rank(TOTAL

RangeSum(

[Success]

/

([Success] + [Failure]),

[# of Docs]/1E5))

[Anonymous]

Thanks Sunny,

Almost there with!

When I amend the expression to suit my business' real life app, I'm getting a few anomalies as you will see from the attached Excel file.

Firstly, Rank 1 shouldn't be NMO, but ESX.

Also, where there are sites with the same score and number of docs, they aren't being given an individual ranking (which is what I was trying to get using the sort by name as the third sort criteria).

The expression I'm using is

Rank(TOTAL

RangeSum(

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

/

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

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

Any ideas?

Many thanks again.

Gareth

[sunny_talwar]

Try this

Rank(TOTAL

RangeSum(

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

/

(Count({<[FTMR_P2P_KPI]={"Success"},[Purchase Order Document Type - Code]-={''} >} Distinct [Invoice Number]) + Count({<[FTMR_P2P_KPI]={"Failure"},[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 [Site Code])/1E10))

[Anonymous]

So close.

The sorting on the score is correct now, it's just some of the ranking is showing as a 7-11 where the scores are the same. Is there anyway of splitting this down to 7, 8, 9, 10, 11?

Thanks,

Gareth