Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
jbragg
Contributor II
Contributor II
‎2019-01-24 02:31 PM

Conditional Ranking

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)
589 Views
0 Likes
5 Replies
Frank_Hartmann
Master II
Master II
‎2019-01-24 02:36 PM

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

586 Views
0 Likes
jbragg
Contributor II
Contributor II
‎2019-01-24 02:44 PM
Author

In response to Frank_Hartmann

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

 

 

582 Views
0 Likes
jbragg
Contributor II
Contributor II
‎2019-01-24 03:18 PM
Author

In response to Frank_Hartmann

I removed the apostrophes... no change.

 

577 Views
0 Likes
Chernov
Creator
Creator
‎2019-01-25 12:03 AM

In response to jbragg

Try aggr with NODISTINCT:

1-st line :

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

560 Views
0 Likes
Sue_Macaluso
Community Manager
Community Manager
‎2019-01-25 12:39 PM

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
Sue Macaluso
550 Views
0 Likes