Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

Re: TOP 3 Values in Text Box

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

9 Replies
Highlighted
bbi_mba_76
Valued Contributor

Re: TOP 3 Values in Text Box

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

)

MVP
MVP

Re: TOP 3 Values in Text Box

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

MVP
MVP

Re: TOP 3 Values in Text Box

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 =

MVP
MVP

Re: TOP 3 Values in Text Box

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

MVP
MVP

Re: TOP 3 Values in Text Box

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

MVP
MVP

Re: TOP 3 Values in Text Box

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

MVP
MVP

Re: TOP 3 Values in Text Box

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)

MVP
MVP

Re: TOP 3 Values in Text Box

No.. .giving wrong COUNT...

MVP
MVP

Re: TOP 3 Values in Text Box

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)