Nested Rank Function

[Anonymous]

Hi All,

We are using Qlik Sense April 2018 version.

We  have a business requirement on rank function.In my qvd i have Emp Name,Sales Value and Revenue value.I want calculate the Rank of Employee first based on Sales Value and then on Revenue .If the Sales value of two employees are same then it should check for revenue value and then calculate the final rank for the employee.

Please find the below example.

CC	Sales	Revenue	Rank(sum(Sales))	Rank(sum(Revenue))	Final Rank(If sales rank is same then check for revenue rank)
AA	5000	3000	1	1	1
BB	4000	1500	2	3	3
CC	4000	2000	2	2	2
DD	3000	1000	3	4	4

Please help me to resolve this issue.

Thanks,

Duryodhan

[sunny_talwar]

May be this

Rank(RangeSum(Sum(Sales), Sum(Revenue)/1E10))

[sunny_talwar]

Sample image and file attached

[rangam_s]

Check this app.

if(SubStringCount(Rank(Sum(Sales)),'-')>=1,Rank(Sum(Revenue)),Rank(Sum(Sales)))

[rangam_s]

or this,

if(SubStringCount(','&Concat(Total (Aggr(Rank(Sum(Sales)),CC)),',')&',',','&Rank(Sum(Sales))&',')>1,Rank(Sum(Revenue)),Rank(Sum(Sales)))