Useful Alternate Sort Method for Listboxes
It’s a Friday so how about a simple but really useful post about how to make certain listboxes that little but more user friendly.
Basically the aim here is to get around the problem you sometimes encounter when you have a long list of values that you want to sort say alphbetically but within the list there are several values that the mojority of people will look for. The most common example of this when progressing through a websites shopping cart and you’re asked to complete the country, whats at the top; Afganistan, Albania, Algeria and American Samoa no doubt – all big internet shopping nations I’m sure; but not compared to lets say the US and UK. Coupled with that countries (just as easily sales people, products etc) can have various derivations of thier name; I’ve lost count of the number of times I’ve scrolled down a country dropdown looking for ‘United Kingdom’ only to find it’s listed under ‘Great Britain’ or ‘England’; not the biggest issue in the world but one we can address in Qlikview where users may need to find values quickly and easily.
So in this example we’re going to pull 2 values (‘United Kingdom’ and ‘United States’) to the top of the list whilst keeping the rest ordered Alphabetically below. To do this simply go to the Sort tab of the listboxes Properties dialog and check the Alphabetical > Ascending button, set the State to Auto Ascending and also check the ‘Expression’ button and enter the following expression:
=if(Country=’United Kingdom’,0,if(Country=’United States’,0,1))
Obviously our listbox dimension is named ‘Country’. This essentailly assigns a value of 0 to our 2 nations and a 1 to the rest meaning that when the expression result is sorted in ascending order they jump to the top making it easy to find them when regularly needs. I’ve also added the dashed line into the actual dataset (simple concatenation onto Country) to create the seperation and make the list more intuative; as it doesn’t relate to the rest of the data selecting it has no impact.
As a slight alternate we could split the field into 2 as in the left hand listboxes by creating expression driven listboxes (select from the bottom of the field list) with either of the following 2 expressions:
=if(Country=’United Kingdom’ or Country=’United States’,Country)
=if(Country<>’United Kingdom’ or Country <>’United States’,Country)
Personally I’m not a fan of this second method as it makes it hard to select from both listboxes.
Obviously if you want to pull many values to the top this solution isn’t the best; to do that you should create a numeric order mapping and then use the dual() function to sort the listbox by the mapped value.
Hope you can put it to use.
All the best,