Firstsortedvalue for Tied values

[GeorgePhilips2302]

Attached file contains a pivot table in excel format.

I have to use a Firstsortedvalue function to return the product name with minimum Count()

Since there are three products with the same value, a null is returned.

So I want the Product name which is least in count and the least in Sum(Sales) as the output of my firstsorted function.

Please help me frame the formula!

[swuehl · Accepted solution]

Try to use something like the sum of

Count() - 1 /  Sum(Sales) 

as the FirstSortedValue() sort weight

[rwunderlich · Accepted solution]

For the record, FirstSortedValue does have an optional DISTINCT parameter that is useful when there is a tie.  Probably not what you want because it just picks the first of the ties. 

In your case, you have a rule you want to use in a tie -- lowest Sales. So add sales as a small bias to your expression like:

FirstSortedValue(ProductName,  Min( Aggr(Count(ProductName) + (Sum(CustomerSales) * .000001),ProductName))

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[GeorgePhilips2302]

Both the solutions worked.

Swuehl's : FirstSortedValue(ProductName, Aggr(Count(ProductName)-1 / (Sum(CustomerSales)),ProductName))

and

Rwunderlich's : FirstSortedValue(ProductName, Aggr(Min( Aggr(Count(ProductName) + (Sum(CustomerSales) * .000001),ProductName)),ProductName))

Thanks a lot guys!