How to create custom sort order in Bold BI

Sorting is the process of applying some order to a collection of items. BoldBI allows you to customize the sorting behavior of dimension fields in each widget. You can order them based on alphabet or value, data source order, or field.

Sometimes you do not prefer the existing order and you want to sort it in a different order, then you can always choose to Custom Sort your data. Although this is not a default function within Bold BI, you can work around this limitation with creating custom sort order and sort data any way you choose.

To proceeding with this example, consider Bold BI is connected with MS SQL data source and the table named ORDER DETAILS is added as shown in the following screenshot.

In this example, we have configured a column chart to show the details of advance and order amounts for the respective months as below:

Here, we have March through July and it’s formatted as text and does not date specific. So, it would not be sorted January through December. You can see in our sort it’s sorted alphabetically by the first letter of the month. That’s because it doesn’t recognize this as actual month data. From, creating custom sort order function, you can control all of that.

First, you need to create an expression column like below to house your sorting logic.

Sample Expression: IF([ORDER_MONTH] ='MAR', 1, IF([ORDER_MONTH] ='APR', 2, IF([ORDER_MONTH] ='MAY', 3, IF([ORDER_MONTH] ='JUN',4, IF([ORDER_MONTH] ='JUL', 5, 0)))))

Once the Expression is created, Sort the dimension value based on this expression. follow the below steps for sort the dimension field based on the Expression to achieve your requirement.

1. Click the Settings icon available in the dimension field which needs to be sort and select Sort option from the context menu like below.

   

2. The Advanced Sorting Options dialog will open as shown in the following image.

   

3. Configure Field based sorting to Orders the data based on the Min of the Expression field.

   

4. After click Apply option and you can see the sorted column March to July.

   

Related links

Dashboard Designer Walkthrough

Data Transformation

Sort data using custom list

Custom Sort Order in Excel