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)
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:
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)
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:
Lastly, to get store with 2nd highest price for each Product:
Use this function in Chart Expression: FirstSortedValue(Store, -Price, 2)
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.
Here is the LOAD Script for the data used in the examples above. Give it a try yourself!
LOAD * inline [
] (delimiter is ',');