Rank and Set Analysis

[MK_QSL]

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 · Accepted solution]

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]

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]

Cheers !

[maxgro]

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

supposing - is null