Qlik Community

Qlik Sense Cloud Discussions

Highlighted
jbragg
New Contributor

Ranking with nested IF and Set Analysis

This returns the corresponding (and correct) result as I filter by Vendor_Name.  I want to rank the result, but having difficulties.  
 
 
 
 
 
aggr(
if(aggr(sum(workorderflag),Vendor_Name)>50,
aggr(
(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)
+
(avg({<Vendor_Name=>}SuccessScore)*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),Vendor_Name)
,0)

,Vendor_Name)
 
 
 
 
1 Reply
jbragg
New Contributor

Re: Ranking with nested IF and Set Analysis

Ok,  this formula works perfect.

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

aggr(rank(sum({<Vendor_Name=>}workorderflag),Vendor_Name,4),Vendor_Name)

,'tryagain')

 

I thought, i could simply replace the workorderflag field with the long if statement previously posted, but that doesn't work.

 

Community Browser