Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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!
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!
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)
Cheers !
if(match(rank(sum({$<Customer={"*"}>} Sales)),2,3),Customer)
supposing - is null