topic Re: Conditional Ranking in App Development

Conditional Ranking

jbragg — Thu, 24 Jan 2019 19:31:26 GMT

I'm looking to rank my results only if each customer has greater than 50 work orders. I can't seem to get it just right. My current ranking formula, ranks all the customers.

aggr(rank(
(if(avg({<Vendor_Name=>} [Profit Percentage]) >='0' and avg({<Vendor_Name=>} [Profit Percentage])<='.14999',1,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.15' and avg({<Vendor_Name=>} [Profit Percentage])<='.19999',2,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.2' and avg({<Vendor_Name=>} [Profit Percentage])<='.24999',3,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.25' and avg({<Vendor_Name=>} [Profit Percentage])<='.2999',4,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.3' and avg({<Vendor_Name=>} [Profit Percentage])<='1',5,

0))))))*6

if((count({<Vendor_Name=>} workorderflag)-sum({<Vendor_Name=>} VendorRejectFlag))/count({<Vendor_Name=>} workorderflag) >='0' and (count({<Vendor_Name=>} workorderflag)-sum({<Vendor_Name=>} VendorRejectFlag))/count({<Vendor_Name=>} workorderflag) <= '.74999',1,

if((count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) >='.75' and (count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) <= '.84999',2,

if((count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) >='.85' and (count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) <= '.89999',3,

if((count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) >='.90' and (count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) <= '.94999',4,

if((count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) >='.95' and (count({<Vendor_Name=>}workorderflag)-sum({<Vendor_Name=>}VendorRejectFlag))/count({<Vendor_Name=>}workorderflag) <= '1',5,

0)))))*2

((if(floor((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag))*100)>='0' and floor((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag))*100)<='74',1,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='75' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='84',2,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='85' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='89',3,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='90' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='94',4,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='95' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='100',5,

0))))))*2)

((if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='0' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='1440',5,

if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='1441' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='2160',4,

if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='2161' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='2880',3,

if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='2881' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='3360',2,

if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='3361' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='10000',1,

0))))))*2)

((if(floor(Avg({<Vendor_Name=>}SuccessScore))>='0' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='.74',1,

if(floor(Avg({<Vendor_Name=>}SuccessScore))>='.75' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='.84',2,

if(floor(Avg({<Vendor_Name=>}SuccessScore))>='.85' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='.89',3,

if(floor(Avg({<Vendor_Name=>}SuccessScore))>='.9' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='.94',4,

if(floor(Avg({<Vendor_Name=>}SuccessScore))>='.95' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='1',5,

0))))))*2)

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='0' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.74999',1,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.75' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.84999',2,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.85' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.89999',3,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.90' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.94999',4,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.95' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '1',5,

0)))))

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='0' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.74999',1,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.75' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.84999',2,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.85' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.89999',3,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.90' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.94999',4,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.95' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '1',5,

0)))))

(if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='0' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='60',5,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='61' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='90',4,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='91' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='115',3,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='116' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='147',2,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='148' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='240',1,

0)))))*2),4),Vendor_Name)

Re: Conditional Ranking

Frank_Hartmann — Thu, 24 Jan 2019 19:41:22 GMT

what happens if you remove the apostrophes?
with apostrophes QV interpretes it as string not as num!

Re: Conditional Ranking

jbragg — Thu, 24 Jan 2019 19:44:14 GMT

It maybe hard to tell, but what I have here are 8 if statements. I'm summing the results of each if statement to get a score. I did fail to copy and paste correctly, so here's the entire expression. The ranking of the 8 if statements works correctly, it returns a rank of 1 to 93. But there are only 35 customers with >50 work orders.. so the ranking should result in 1 to 35, ignoring the 58 customers with less than 50 work orders.

if(aggr(sum(workorderflag),Vendor_Name)>'50',

aggr(rank(
(if(avg({<Vendor_Name=>} [Profit Percentage]) >='0' and avg({<Vendor_Name=>} [Profit Percentage])<='.14999',1,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.15' and avg({<Vendor_Name=>} [Profit Percentage])<='.19999',2,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.2' and avg({<Vendor_Name=>} [Profit Percentage])<='.24999',3,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.25' and avg({<Vendor_Name=>} [Profit Percentage])<='.2999',4,

if(avg({<Vendor_Name=>} [Profit Percentage]) >='.3' and avg({<Vendor_Name=>} [Profit Percentage])<='1',5,

0))))))*6

0)))))*2

((if(floor((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag))*100)>='0' and floor((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag))*100)<='74',1,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='75' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='84',2,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='85' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='89',3,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='90' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='94',4,

if(floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)>='95' and floor(((sum({<Vendor_Name=>}OnTimeFlag)/sum({<Vendor_Name=>}workorderflag)))*100)<='100',5,

0))))))*2)

((if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='0' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='1440',5,

if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='2881' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='3360',2,

if(floor(Avg({<Vendor_Name=>}SubmittionTime))>='3361' and (floor(Avg({<Vendor_Name=>}SubmittionTime)))<='10000',1,

0))))))*2)

((if(floor(Avg({<Vendor_Name=>}SuccessScore))>='0' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='.74',1,

if(floor(Avg({<Vendor_Name=>}SuccessScore))>='.9' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='.94',4,

if(floor(Avg({<Vendor_Name=>}SuccessScore))>='.95' and (floor(Avg({<Vendor_Name=>}SuccessScore)))<='1',5,

0))))))*2)

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='0' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.74999',1,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.75' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.84999',2,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.85' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.89999',3,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.90' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '.94999',4,

if((sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) >='.95' and (sum({<Vendor_Name=>}AcceptFlag))/count({<Vendor_Name=>}workorderflag) <= '1',5,

0)))))

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='0' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.74999',1,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.75' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.84999',2,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.85' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.89999',3,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.90' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '.94999',4,

if((sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) >='.95' and (sum({<Vendor_Name=>}ResponseFlag))/count({<Vendor_Name=>}workorderflag) <= '1',5,

0)))))

(if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='0' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='60',5,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='61' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='90',4,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='91' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='115',3,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='116' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='147',2,

if(avg({<Vendor_Name=>}[ResponseTime Minutes])>='148' and avg({<Vendor_Name=>}[ResponseTime Minutes]) <='240',1,

0)))))*2),4),Vendor_Name)
,)

Re: Conditional Ranking

jbragg — Thu, 24 Jan 2019 20:18:55 GMT

I removed the apostrophes... no change.

Re: Conditional Ranking

Chernov — Fri, 25 Jan 2019 05:03:45 GMT

Try aggr with NODISTINCT:

1-st line :

if(aggr( NODISTINCT sum(workorderflag),Vendor_Name)>'50',

Re: Conditional Ranking

Sue_Macaluso — Fri, 25 Jan 2019 17:39:00 GMT

I am new to Qlik and would like to get this to the correct forum. Which Qlik product are you referring to and I will make sure this post gets to the correct one. Thanks @jbragg