topic Re: Rank Help in QlikView

Rank Help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Rank Help

Clever_Anjos — Wed, 04 Oct 2017 13:12:54 GMT

Wich expression is "Success %" ?

Re: Rank Help

Anonymous — Wed, 04 Oct 2017 13:14:55 GMT

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

Thanks

Re: Rank Help

sunny_talwar — Wed, 04 Oct 2017 13:17:58 GMT

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

Re: Rank Help

Clever_Anjos — Wed, 04 Oct 2017 13:19:55 GMT

and "Success" and "Failure"?

Re: Rank Help

Anonymous — Wed, 04 Oct 2017 13:33:34 GMT

Hi,

QVW Sample attached.

Thanks,

Gareth

Re: Rank Help

sunny_talwar — Wed, 04 Oct 2017 13:37:17 GMT

May be this

Rank(TOTAL

RangeSum(

[Success]

([Success] + [Failure]),

[# of Docs]/1E5))

Re: Rank Help

Anonymous — Wed, 04 Oct 2017 14:12:10 GMT

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

Re: Rank Help

sunny_talwar — Wed, 04 Oct 2017 14:21:45 GMT

Try this

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])/1E8,

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

Re: Rank Help

Anonymous — Wed, 04 Oct 2017 14:25:55 GMT

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

Re: Rank Help

sunny_talwar — Wed, 04 Oct 2017 15:20:45 GMT

Can you share the output you are seeing?

Re: Rank Help

Anonymous — Wed, 04 Oct 2017 15:42:16 GMT

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

Re: Rank Help

sunny_talwar — Wed, 04 Oct 2017 16:17:53 GMT

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