Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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
Partner

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

View solution in original post

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)