Qlikview FirstSortedValue

In the following post I will demonstrate how to use the Qlikview FirstSortedValue chart function.  This is a useful tool when trying to determine the min/max value for a specific field, based on a given dimension.

From Qlikview Help:  FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument, taking into account rank, if specified. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL.

Setup – Qlikview FirstSortedValue

Let’s say you have the following table (Load Script for INLINE table can be found at the end of this post)

Qlikview FirstSortedValue

So, you would like to know which Store has the lowest price for each Product.  Using the Qlikview FirstSortedValue chart function, this is simple:

Use this function in Chart Expression:  FirstSortedValue(Store, Price)

Results in the following Chart Output:

Qlikview FirstSortedValue

Now, let’s say you’d like to see the store with 2nd lowest price for each Product:

Use this function in Chart Expression:  FirstSortedValue(Store, Price, 2)

Qlikview FirstSortedValue

Now, you’d like to see the store with the highest price for each Product:

Use this function in Chart Expression:  FirstSortedValue(Store, -Price)

This results in the following Chart:

Qlikview FirstSortedValue

Lastly, to get store with 2nd highest price for each Product:

Use this function in Chart Expression:  FirstSortedValue(Store, -Price, 2)

Qlikview FirstSortedValue

Breaking Down the Function

Breaking down the function syntax:  FirstSortedValue(value, sort_weight, rank)

  • Value – Output Field.  This is the actual field name you want to return in your chart – In my examples above, I used the field “Store”
  • sort_weight – Input Field.  This is the data field used in sort expression that determines output field value – In my examples above, I used the field “Price”.  The default sort is Ascending.  Put a “-” minus sign in front of the sort_weight field to get function to sort Descending (i.e.  “-Price”)
  • rank – This argument is optional.  Default is 1 (top rank).  By adding any value (n) as the rank, you get the nth ranked Output Field.  In the 3rd and 4th examples above, I used the rank “2” to get the second highest and lowest Output Fields respectively.

LOAD Script

Here is the LOAD Script for the data used in the examples above.  Give it a try yourself!

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

4 thoughts on “Qlikview FirstSortedValue

    • Hello Devarasu, looks like the link with your same question on Qlik Community provided a couple alternatives for your NULL issue. The Qlik Community is a great place to find just about any answer. Thanks for sharing on my site. Sorry I didn’t get back to you in time, but it looks like you are all set. Just reply if you have any other questions.

Leave a Comment