topic Re: Rank and Set Analysis in QlikView

Rank and Set Analysis

MK_QSL — Fri, 27 Jun 2014 13:08:52 GMT

I have below table...

Load * Inline

[

Customer, Sales

A, 100

-, 120

-, 200

B, 30

C, 10

];

I need Customer having 2nd and 3rd highest sales ignoring no customer i.e. '-'

The actual application is different and I can't do any changes in the script.. This should be done only in UI side...

I am trying below to get the top customer... need 2nd and 3rd also...

=Only({<Customer = {"=Rank(SUM(Sales))=1"}>}Customer)

jerem1234 — Fri, 27 Jun 2014 13:20:40 GMT

Maybe like:

=only({<Customer = {"=Rank(SUM({<Customer -= {'-'}>}Sales))=2"}>}Customer)

or if they are nulls or blanks:

=only({<Customer = {"=Rank(SUM({<Customer = {'=len(trim(Customer))>0'}>}Sales))=2"}>}Customer)

Hope this helps!

jerem1234 — Fri, 27 Jun 2014 13:28:31 GMT

Maybe you could also try out firstsortedvalue:

=firstsortedValue(Customer, -aggr(sum({<Customer -= {'-'}>}Sales), Customer), 1)

=firstsortedValue(Customer, -aggr(sum({<Customer -= {'-'}>}Sales), Customer), 2)

=firstsortedValue(Customer, -aggr(sum({<Customer -= {'-'}>}Sales), Customer), 3)

MK_QSL — Fri, 27 Jun 2014 13:40:22 GMT

Cheers !

maxgro — Fri, 27 Jun 2014 13:43:00 GMT

if(match(rank(sum({$<Customer={"*"}>} Sales)),2,3),Customer)

supposing - is null