Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
devarasu07
Master II
Master II

firstsortedvalue with group by?

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?

Best Color.jpg

1 Solution

Accepted Solutions
sunny_talwar

Try this:

=Concat(DISTINCT Aggr(If(Sum(SoldQty) = Max(TOTAL <Product> Aggr(Sum(SoldQty), Product, Color)), Color), Product, Color), ', ')

View solution in original post

3 Replies
sunny_talwar

Try this:

=Concat(DISTINCT Aggr(If(Sum(SoldQty) = Max(TOTAL <Product> Aggr(Sum(SoldQty), Product, Color)), Color), Product, Color), ', ')

sunny_talwar

For any one color, you can use FirstSortedValue() function with DISTINCT

=firstsortedvalue(distinct Color,-aggr(sum(SoldQty),Product,Color))

trdandamudi
Master II
Master II

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))