How to create a custom sort of items in a listbox (page control, filter, parameter)

By default, listboxes in Sigma page controls sort items by the number of rows, associated with each item, like this:

page control - listbox items

But what if I want to sort the items alphabetically? While there is no formatting option for that at the moment (3/16/2022), there is a way to accomplish that.

To just have a simply alphabetic, ascending sort, simply create a data source (like perhaps a table element with grouping level), which will have 1 row per row. In the example above, just create a table element with 1 row per Country:

You can then use that table element as the source of the listbox, and all countries will be displayed alphabetically, like this:

page control listbox with simple alpha sort

But what if I want countries to be sorted in a descending order, or with some other, more complex sort? That is also possible.

We will create a dataset, and, inside of it, use a special, histogram-based, fanout SQL ( download a Snowflake version of the script from here ) .

In this dataset, we have 2 data sources (I will use the list of countries as an example):

Source 1 - your list of countries, 1 row per country, sorted in any way you like, using the [Sort Order] that you create a populate. Here, you can use SQL or Sigma formulas using Rank(), in order to populate Sort Order. In my example, I decided to sort countries in the descending order, so I used this sql:

SELECT COUNTRY, SEQ4()+1 SORT_ORDER
FROM 
(
SELECT DISTINCT COUNTRY
FROM SIGMASUPPORT.RETAIL_PRODUCT_SALES.ORDERS_PRODUCTS_CUSTOMERS 
  ORDER BY 1 -- if you want sort descending by Country in the final dataset, use the opposite (ASC sort here)
  )

Source 2 - the custom SQL with the histogram-based fanout (see above).

Join Source 2 as follows:
LEFT JOIN source1.SORT_ORDER = source2.VALUE_CT

The resulting dataset will look like this:

Now, if you use this dataset as the source for your listbox, it will sort Countries alphabetically, in the descending order.

1 Like