How to rank duplicate values

[farheenayesha]

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. @

[petter]

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/RankingFu...

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

[stevedark]

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