TOP 3 Values in Text Box

[MK_QSL]

Hello Experts,

Need your help....

Objective is to display top three customers in below Text Box based on

1) COUNT of DISTINCT INVOICES

2) BRAND only B1 and B5

3) CATEGORY only C1 and C2

4) INVOICEs having NO CUSTOMER names should be ignored...

            i.e. NOCUSTOMER should not be displayed in TOP 3

5) The Result should be Fixed so that even though on selection  of CUSTOMER from CUSTOMER List Box would not reflect the result.

Sample File enclosed ....

[bbi_mba_76]

Hi,

maybe using something like

=concat(if(aggr(rank(aggr(count(distinct if(len(CUSTOMER)>1 and match(BRAND,'B1','B5')>0 and match(CATEGORY,'C1','C2')>0, INVOICE_NO)),  CUSTOMER),3,1), CUSTOMER)<=3,

CUSTOMER),','

)

[tresesco]

=Concat( {1<CUSTOMER={"=Rank(Count(DISTINCT {1<BRAND={'B1', 'B5'}, CATEGORY={'C1','C2'}, INVOICE_NO=e({<CUSTOMER={''}>})>}INVOICE_NO))<4"}>} DISTINCT CUSTOMER, ',')

[MK_QSL]

Both answers are correct but the problem is (sorry to forgot initially) I want to show in three different text boxes ...

Text Box 1

Top 1 Customer =

Tex Box 2

Top 2 Customer =

Text Box 3

Top 3 Customer =

[tresesco · Accepted solution]

Top 1 Cust:  =Concat( {1<CUSTOMER={"=Rank(Count(DISTINCT {1<BRAND={'B1', 'B5'}, CATEGORY={'C1','C2'}, INVOICE_NO=e({<CUSTOMER={''}>})>}INVOICE_NO))=1"}>} DISTINCT CUSTOMER, ',')

Top 2,3 Cust: = change only highlighted part to =2 and =3 respectively

[MK_QSL]

It's DONE... !!!

=Only( {1<CUSTOMER={"=Rank(Count(DISTINCT {1<BRAND={'B1', 'B5'}, CATEGORY={'C1','C2'}, INVOICE_NO=e({<CUSTOMER={''}>})>}INVOICE_NO))=1"}>} CUSTOMER)

need to change 1 with 2 and 3 with other text box

Thank you both...

[MK_QSL]

One more question....?

How to show the Invoice Count also for these three Customers?

i.e.

Text Box 1

CustomerName = TotalDistinctInvoices

Tex Box 2

CustomerName = TotalDistinctInvoices

Text Box 3

CustomerName = TotalDistinctInvoices

TOP 3 Values in Text Box

[tresesco]

For first customer:=Count( {1<CUSTOMER={"=Rank(Count(DISTINCT {1<BRAND={'B1', 'B5'}, CATEGORY={'C1','C2'}, INVOICE_NO=e({<CUSTOMER={''}>})>}INVOICE_NO))=1"}>} DISTINCT INVOICE_NO)

[MK_QSL]

No.. .giving wrong COUNT...

[MK_QSL]

DONE

=Count( {1<CUSTOMER={"=Rank(Count(DISTINCT {1<BRAND={'B1', 'B5'}, CATEGORY={'C1','C2'}, INVOICE_NO=e({<CUSTOMER={''}>})>}INVOICE_NO))=1"}, BRAND = {'B1','B5'}, CATEGORY = {'C1','C2'}>} DISTINCT INVOICE_NO)