Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
LOAD * INLINE [
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....
You can use this
=Concat(DISTINCT Aggr(If(truckQty = Max(TOTAL <Product> truckQty), Unit), Product, Unit), ', ')
May be like this
FirstSortedValue(Distinct Unit, -(truckQty+(unitQty/1E10)))
Sample attached
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' ?
You can use this
=Concat(DISTINCT Aggr(If(truckQty = Max(TOTAL <Product> truckQty), Unit), Product, Unit), ', ')
Brilliant! Thank you for your help and for responding so quickly.
Hello sunny. Is there any more performant way to do that? Thank you very much.