topic Re: Firstsortedvalue for Tied values in QlikView

Firstsortedvalue for Tied values

GeorgePhilips2302 — Thu, 19 Jan 2023 12:44:21 GMT

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!

Re: Firstsortedvalue for Tied values

swuehl — Thu, 19 Jan 2023 16:57:19 GMT

Try to use something like the sum of

Count() - 1 / Sum(Sales)

as the FirstSortedValue() sort weight

Re: Firstsortedvalue for Tied values

rwunderlich — Thu, 19 Jan 2023 17:03:49 GMT

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

Re: Firstsortedvalue for Tied values

GeorgePhilips2302 — Fri, 20 Jan 2023 05:46:27 GMT

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!