Rank Function with two measures

[chaitanyajami]

Hi all,

I have measures service request count as  count([Service Request ID]) and

MPS utilization percentage as  ((count(if([Origin Flag] ='MPS',[Service Request ID]))/count([Service Request ID])))

i need to show account names that is having top 5 service request count in descending order  and for those top 5 accounts i need to show MPS utilization in ascending order , I'm using below expression but could not achieve it, can someone please help me achieving it.

if(rank(aggr(count([Service Request ID]),[Account Name])<=5,Rank(aggr(-(count(if([Origin Flag] ='MPS',[Service Request ID]))/

count([Service Request ID]))),[Account Name])

or

Rank(aggr({<rank(aggr(count([Service Request ID]),[Account Name])<=5>}-(count(if([Origin Flag] ='MPS',[Service Request ID]))

/count([Service Request ID]))0,1))

[sunny_talwar]

Would you be able to share a sample here with your expected output?

[chaitanyajami]

Hi Sunny

like below

account name  sr count  Mpi %

a                        5          2%

d                        6          3%

b                        7           4%

c                        3           7%

d                        4           9%

e                        2           8%         

f                         1          4%

g                         9         11%

h                         2          13%

top five accounts with sr count in descending order

accnt sr_count MPI

h          9            11%

c          7             4%

b          6             3%

a          5              2%

e          4              9%

i want to show above accounts MPI %  in asceding order

a  2%

b  3%

c  4%

e  9%

h  11%

[sunny_talwar]

I think the ascending order is a sorting thing which can be handled on the sort tab. Try this to get the percentage here:

If(Rank(Count([Service Request ID]) < 6,

Count({<[Origin Flag] *= {'MPS'}>} [Service Request ID])/Count([Service Request ID]))

[chaitanyajami]

Thanks a ton, i am using it in Qliksense, may be i have to use the options in chart sorting > Expression>

Sort numerically > asceding

I will implement and check it

[chaitanyajami]

Hi ,

Im getting error like  Expression takes 2-3 parameters, any idea what is it

[sunny_talwar · Accepted solution]

I think I missed a parenthesis, try this:

If(Rank(Count([Service Request ID])) < 6,

Count({<[Origin Flag] *= {'MPS'}>} [Service Request ID])/Count([Service Request ID]))

[chaitanyajami]

Thanks, Its working