FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument, for example, the name of the product with the lowest unit price. The nth value in the sort order, can be specified in rank. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL.
Output field. The function finds the value of the expression value that corresponds to the result of sorting sort_weight.
sort_weight
Input field. The expression containing the data to be sorted. The first (lowest) value of sort_weight is found, from which the corresponding value of the value expression is determined. If you place a minus sign in front of sort_weight, the function returns the last (highest) sorted value instead.
rank
By stating
a rank "n" larger than 1, you get the nth sorted value.
SetExpression
By default, the aggregation function
will aggregate over the set of possible records defined by the selection.
An alternative set of records can be defined by a set analysis
expression.
DISTINCT
If the word
DISTINCT occurs before the function
arguments, duplicates resulting from the evaluation of the function arguments are disregarded.
TOTAL
If the word
TOTAL occurs before the function
arguments, the calculation is made over all possible values given
the current selections, and not just those that pertain to the current dimensional value, that is, it disregards the chart dimensions.
By using TOTAL [<fld {.fld}>], where the TOTAL qualifier is followed by a list of one or more field names as a subset of the chart dimension variables, you create a subset of the total possible values.
Load the data and open a sheet. Create a new table and add these fields as measures:
=FirstSortedValue (Product, UnitPrice), to calculate the product with the lowest unit price.
=FirstSortedValue (Product, UnitPrice, 2), to calculate the product with the second-lowest unit price.
=FirstSortedValue (Customer, -UnitPrice, 2) to calculate the customer with the product that has the second-highest unit price.
=FirstSortedValue (Customer, UnitPrice, 3) to calculate the customer with the product that has the third-highest unit price.
=FirstSortedValue (Customer, -UnitPrice*UnitSales, 2) to calculate the customer with the second-highest sales order value, UnitPrice multiplied by UnitSales.
The first column returns the value BB as this is the product which has the lowest UnitPrice (9)
The second column returns the value BB as this is the product with the second lowest UnitPrice (10).
The third column returns the value Betacab, which is the Customer with the Product that has the second-highest UnitPrice (20).
The fourth column returns NULL, because there are two values of Customer (Astrida and Canutility) with the same rank (third-lowest) UnitPrice (15).
The fifth column returns Canutility, which is the Customer with the second-highest sales order value, UnitPrice multiplied by UnitSales (120).
Example - FirstSortedValue scenario
Overview
A dataset contains sales figures for the first three months of the year. The regional manager wants to identify the best and worst selling products to plan for future inventory.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new table and add this field as a dimension:
Category
Create the following measures:
=FirstSortedValue(SalesProduct, -Sales), to calculate the top selling product in each category.
=FirstSortedValue(TOTAL SalesProduct, -Sales), to calculate the top selling product overall.
=FirstSortedValue(TOTAL SalesProduct, Sales) to calculate the lowest selling product overall.
Results table
Category
FirstSortedValue(SalesProduct, -Sales)
FirstSortedValue(TOTAL SalesProduct, -Sales)
FirstSortedValue(TOTAL SalesProduct, Sales)
Fruit
Apple
Apple
Carrot
Vegetable
Carrot
Apple
Carrot
The results indicate that Apple and Carrot are the best selling products in their respective categories, Fruit and Vegetable. However, across all categories, Apple was the best selling product and Carrot was the worst-selling product.
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.