topic Re: Rank Function with two measures in QlikView

Rank Function with two measures

chaitanyajami — Tue, 15 Nov 2016 10:20:54 GMT

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])

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))

Re: Rank Function with two measures

sunny_talwar — Tue, 15 Nov 2016 10:33:46 GMT

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

Re: Rank Function with two measures

chaitanyajami — Tue, 15 Nov 2016 11:09:39 GMT

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%

Re: Rank Function with two measures

sunny_talwar — Tue, 15 Nov 2016 11:14:00 GMT

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]))

Re: Rank Function with two measures

chaitanyajami — Tue, 15 Nov 2016 11:25:24 GMT

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

Re: Rank Function with two measures

chaitanyajami — Tue, 15 Nov 2016 11:38:35 GMT

Hi ,

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

Re: Rank Function with two measures

sunny_talwar — Tue, 15 Nov 2016 11:56:00 GMT

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]))

Re: Rank Function with two measures

chaitanyajami — Tue, 15 Nov 2016 12:23:03 GMT

Thanks, Its working