topic How to rank duplicate values in QlikView

How to rank duplicate values

farheenayesha — Wed, 04 Jul 2018 11:15:04 GMT

Hi,

I want to rank duplicate values with the same rank. Please find the data and expression below.

Region	% to PY	Rank
Region 1	107%	1
Region 2	104%	2
Region 3	103%	3
Region 4	101%	4
Region 5	102%	5
Region 6	100%	6
Region 7	100%	6
Region 8	100%	6
Region 9	99%	7
Region 10	99%	7
Region 11	98%	8
Region 12	97%	9

Expression:

% to PY : Sum({<[Region Name]=>} [Sales Amount])/sum({<[Region Name]=>} [Prior Year Sales Amount])

Rank: Only({<[Region Name]={"Region 6"}>} Aggr(Rank(Sum({<[Region Name]=>} [Sales Amount])/sum({<[Region Name]=>} [Prior Year Sales Amount])), [Region Name]))

However with above Rank expression i am getting Rank as 7 for Region_7. But i want to get the rank as 6 for Region_7. Please help me. @

Re: How to rank duplicate values

petter — Wed, 04 Jul 2018 13:17:14 GMT

The Rank() function can take more than one parameter. With additional parameters you can control exactly how you want the ranking to be done:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/RankingFunctions/rank.htm

Note especially the second parameter named "mode" in the documentaion.

Re: How to rank duplicate values

stevedark — Fri, 27 May 2022 16:02:52 GMT

Just in case it is still useful to anyone, my solution over here may help with this one also:

https://community.qlik.com/t5/App-Development/Change-row-color-when-change-value/m-p/1936581#M77196

You just need to add to the CONCAT statement a sort weight of the expression you are ranking on and it should work.

Cheers,

Steve