topic Re: TOP 3 Values in Text Box in QlikView

TOP 3 Values in Text Box

MK_QSL — Thu, 05 Jun 2014 09:44:34 GMT

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

Re: TOP 3 Values in Text Box

bbi_mba_76 — Thu, 05 Jun 2014 10:21:16 GMT

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

)

Re: TOP 3 Values in Text Box

tresB — Thu, 05 Jun 2014 10:22:13 GMT

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

Re: TOP 3 Values in Text Box

MK_QSL — Thu, 05 Jun 2014 10:26:08 GMT

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 =

Re: TOP 3 Values in Text Box

tresB — Thu, 05 Jun 2014 10:30:43 GMT

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

Re: TOP 3 Values in Text Box

MK_QSL — Thu, 05 Jun 2014 10:31:06 GMT

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

Re: TOP 3 Values in Text Box

MK_QSL — Thu, 05 Jun 2014 11:11:27 GMT

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

Re: TOP 3 Values in Text Box

tresB — Thu, 05 Jun 2014 11:17:07 GMT

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)

Re: TOP 3 Values in Text Box

MK_QSL — Thu, 05 Jun 2014 11:24:01 GMT

No.. .giving wrong COUNT...

Re: TOP 3 Values in Text Box

MK_QSL — Thu, 05 Jun 2014 11:32:18 GMT

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)