
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Firstsortedvalue for Tied values
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!
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try to use something like the sum of
Count() - 1 / Sum(Sales)
as the FirstSortedValue() sort weight


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try to use something like the sum of
Count() - 1 / Sum(Sales)
as the FirstSortedValue() sort weight


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
