Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Requirement:
Need find the Best Color with the highest Sold Qty for each Product.
What i did is,
used below expression and able to get the best color by product wise
=firstsortedvalue(Color,-aggr(sum(SoldQty),Product,Color))
Issue:
If two color in same rank then it's showing null. is there anyway to resolve it? or just show any of one color instead of null?
Try this:
=Concat(DISTINCT Aggr(If(Sum(SoldQty) = Max(TOTAL <Product> Aggr(Sum(SoldQty), Product, Color)), Color), Product, Color), ', ')
Try this:
=Concat(DISTINCT Aggr(If(Sum(SoldQty) = Max(TOTAL <Product> Aggr(Sum(SoldQty), Product, Color)), Color), Product, Color), ', ')
For any one color, you can use FirstSortedValue() function with DISTINCT
=firstsortedvalue(distinct Color,-aggr(sum(SoldQty),Product,Color))
Also you can try the below expression: ( One of Sunny's post inspired me with this expression ). Hope this helps...
=firstsortedvalue(Color,-aggr(sum(SoldQty) + Rank([Color])/1E10,Product,Color))