Scribd
Upload
41 views2 pages

Using FIRSTSORTEDVALUE for MAX/MIN Sales

The document discusses using the FIRSTSORTEDVALUE function in Qlik to find the dimension value (country) that corresponds to the maximum or minimum value of an expression (sum of sales). It explains that FIRSTSORTEDVALUE sorts on a sort-weight field and returns the corresponding value of another field. To find the country with maximum sales, it uses -Aggr(sum(Sales),Country) as the sort-weight, and to find the minimum it removes the minus sign. Using DISTINCT is recommended in case multiple countries have the same maximum or minimum sales.

Uploaded by

vam_1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
41 views2 pages

Using FIRSTSORTEDVALUE for MAX/MIN Sales

The document discusses using the FIRSTSORTEDVALUE function in Qlik to find the dimension value (country) that corresponds to the maximum or minimum value of an expression (sum of sales). It explains that FIRSTSORTEDVALUE sorts on a sort-weight field and returns the corresponding value of another field. To find the country with maximum sales, it uses -Aggr(sum(Sales),Country) as the sort-weight, and to find the minimum it removes the minus sign. Using DISTINCT is recommended in case multiple countries have the same maximum or minimum sales.

Uploaded by

vam_1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Fistsortedvalue

Imagine you have the following table:


FieldA, FieldB
A, 3
B, 4
C, 1
D, 2

If you run a FirstSortedValue(FieldA, FieldB) you'll get the first sorted value from FieldA based on the
sorting of FieldB. Value 1 is the first value for FieldB so the function will in this example return C.

How to find a dimension value which corresponds to
MAX or MIN of an expression

Scenario:
We need to find the countries who have the MAX and MIN sales and store them in variables to use
in set analysis.




Solution:
FIRSTSORTEDVALUE function can sort the fields according to a sort-weight (another field or an
aggregation) and return the corresponding value of the field.


In our case we need to sort the countries by their total sales, thus we will
use AGGR(sum(sales),country) as our sort-weight in FIRSTSORTEDVALUE function:

vCountryWithMaxSales =FirstSortedValue(Country, -Aggr(sum(Sales),Country))


The above expression returns the country which has the MAX sales.


By default, the FIRSTSORTEDVALUE function sorts in ascending order. For sorting in descending
order, we use the minus sign in front of the sort-weight.


So, if we would like to get the country which has the MIN sales, we can just remove the minus sign:

vCountryWithMinSales =FirstSortedValue(Country, Aggr(sum(Sales),Country))


Warning:
If more than one country have the same MAX or MIN sales, then the FIRSTSORTEDVALUE
function will return NULL. To avoid this, we can use DISTINCT qualifier:

vCountryWithMaxSales=FirstSortedValue(Distinct Country,-
Aggr(sum(Sales),Country))
vCountryWithMinSales=FirstSortedValue(Distinct Country,-
Aggr(sum(Sales),Country))

[Link]

You might also like