having trouble on selecting top 10 customer via button

[Irvin182]

Hi I need help, I'm working on button top 10 customers that will select in field Customer_No that's arrange in order descending [Total Scores] and descending [Total MSF].

the problem is I don't have any basis to get the total score and total msf to select the customer.

Thanks,

Irvin

[tresesco]

Try the below search string:

='=Rank(sum(MSF))<=10'

[Irvin182]

thank you sir, but how about if you have a scenario like this you need to sort also total score

[jaibau1993]

Hi!

Use the following search string in your button action:

='("' & Concat(Aggr(
Only({<Customer_No = {"=Rank(Sum(MSF)) <= 10"}>} Customer_No)
,Customer_No
),'"|"') & '")'

Regards,

Jaime.

[Irvin182]

hi Jaibau1993 in your expression can we add aggregate for the Total Score?

[jaibau1993]

Hi!

What do you actually mean 'add aggregate for the Total Score'? What is your desired output when pressing the button?

Jaime

[jaibau1993]

If you are looking for "the first ten customers sorting by Total Scores and MSF" then I think the following expression should work (not hard tested, maybe fails in some case):

='= Rank(MAX(ComplaintsScore)+MAX(ContractScore)+MAX(Faults)+max(IdentifiedRisk)) <=10 and Rank(Sum(MSF)) <= 10'

or

='("' & Concat(Aggr(
Only({<Customer_No = {"=Rank(Sum(MSF)) <= 10 and Rank(MAX(ComplaintsScore)+MAX(ContractScore)+MAX(Faults)+max(IdentifiedRisk)) <=10"}>} Customer_No)
,Customer_No
),'"|"') & '")'

[Irvin182]

Hi Jaime, 

Here's the output i desired. The output should start with Total Score sort is descending then the Total MSF sort descending.

Customer_ID	Total_Score	Total_MSF
A	15	33,321,122.00
B	15	1,213,231.00
C	15	21,222.00
D	13	323,222.00
E	13	2,321.00
F	7	412,222.00
G	7	13,123.00
H	7	1,212.00
I	6	123,131.00
J	6	123.00
K	4	41,444.00
L	4	23.00
M	2	12,313.00

Regards,

Irvin

[jaibau1993]

Hi!

Okey, I finally understood you. Try the following:

'=Rank(MAX(ComplaintsScore)+MAX(ContractScore)+MAX(Faults)+max(IdentifiedRisk)) <= 10 and rank(Sum({<Customer_ID = {"=Rank(MAX(ComplaintsScore)+MAX(ContractScore)+MAX(Faults)+max(IdentifiedRisk)) <= 10"}>} MSF)) <= 10'

It works with the example sample you provided!

Regards,

Jaime.

[Irvin182]

Hi Jaime,

Thank you for the reply, I've tried the script and the result is this.

when i inseted 1-6 its ok, but when i inseted 7 and above it the sorting didn't work. 😞