5 Replies Latest reply: Apr 27, 2017 10:34 AM by Jeff Clevenger

# firstsortedvalue and concat

I am trying to add an expression in a chart that shows 'Unit' grouped by Product with the max truckQty (and max unitQty). Or even concat multiple results if it returns more than 1 result for max truckQty.

Sample Data:

Product, Unit, truckQty, unitQty
a, aa, 10000, 100
a, bb, 5000, 200
a, cc, 7000, 100
a, dd, 10000, 250
b, aa, 10000, 200
b, bb, 8000, 300
b, cc, 5000, 250
b, dd, 11000, 250
]
;

FirstSortedValue(distinct Unit, -truckQty) returns the following as expected.

a | aa

b | dd

However, my question is how do I get 'a' to return 'dd' (largest truck qty and largest unit qty). I have tried the aggr function but have not been able to get the expected results.

Thank you in advance for taking the time to help me....

• ###### Re: firstsortedvalue and concat

May be like this

FirstSortedValue(Distinct Unit, -(truckQty+(unitQty/1E10)))

• ###### Re: firstsortedvalue and concat

Sample attached

• ###### Re: firstsortedvalue and concat

That works, thank you so much.

One more question... what if I wanted to include both values for max truckQty of 'a' as 'dd,aa' ?

• ###### Re: firstsortedvalue and concat

You can use this

=Concat(DISTINCT Aggr(If(truckQty = Max(TOTAL <Product> truckQty), Unit), Product, Unit), ', ')

• ###### Re: firstsortedvalue and concat

Brilliant! Thank you for your help and for responding so quickly.