Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK_QSL
MVP
MVP

TOP 3 Values in Text Box

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 ....

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

9 Replies
bbi_mba_76
Partner - Specialist
Partner - Specialist

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
MVP
MVP

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

MK_QSL
MVP
MVP
Author

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
MVP
MVP

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
MVP
MVP
Author

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
MVP
MVP
Author

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

tresesco
MVP
MVP

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
MVP
MVP
Author

No.. .giving wrong COUNT...

MK_QSL
MVP
MVP
Author

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)