How to Fix Incorrect Sorting for Calculated Fields (overDueDays) in Grid Widget

Overview

This article explains the root cause of the issue and provides recommended solutions to ensure correct numeric sorting in Bold BI.
If a calculated field such as overDueDays is sorted incorrectly in a Grid widget (for example, 1, 10, 11, 2, 20 instead of 1, 2, 3, 10, 11, 20), the field is typically being treated as a string (text) rather than a numeric value.

Root Cause
This issue occurs when the calculated field returns a text value instead of a number.
Common causes include:

• Functions such as REGEXP_SUBSTR() return text values.
• Measure formatting settings (Number, Currency, Decimal Places) only affect display formatting and do not change the underlying data type.
• Grid widgets sort values based on the actual data type of the field.

Result

Step-by-Step Guide

1. Confirm the Issue

• Open your dashboard in the Bold BI Dashboard Designer.
  
  
• Navigate to the Grid widget that contains the overDueDays field.
• Click on the overDueDays column header to apply ascending sorting.
  
  
• Observe the sorting order of the values.
  If the issue exists, the values may appear in the following incorrect order:
      1, 10, 11, 2, 20
  Instead of the expected numeric order:
      1, 2, 3, 10, 11

Fix A (Recommended): Convert to Numeric value in Data Source

• If possible, modify your query so overDueDays is returned as a number.

-- MySQL
CAST(DATEDIFF(CURDATE(), DueDate) AS SIGNED) AS overDueDays

-- PostgreSQL
CAST((CURRENT_DATE - DueDate) AS INTEGER) AS overDueDays 

• Update the query.
• Save the data source.
• Refresh the widget.

Fix B: Convert the Value in a Bold BI Calculated Field

• If your field uses regex or returns text, convert it to a number:

toNumber(REGEXP_SUBSTR([YourTextColumn], '[0-9]+')) 

• Replace [YourTextColumn] with your actual column name.

Fix C: Use a Numeric Helper Field for Sorting

• Use this approach if the displayed field must remain formatted as text.

Steps:

• Create a helper calculated field:

overDueDays_Sort = toNumber(REGEXP_SUBSTR([YourTextColumn], '[0-9]+')) 

• Add overDueDays_Sort to the Grid as a Hidden Column
• Sort the Grid using this helper field while displaying overDueDays

Fix D: Use “Sort by Specific Field” Option

• Leverage built-in sorting configuration in Bold BI.

Steps:

• Open Grid widget settings
• Set other field sorting to Data Source Order (optional)
• Open sorting settings for your column
• Choose:
• Sort order (Ascending/Descending)
• Based on specific field → select numeric field (overDueDays_Sort)

Validate the Fix

• Sort ascending and confirm numeric order is correct:

1, 2, 3, 8, 10, 11, 15, 20, 25

• Publish the dashboard.
• Verify behaviour in viewer mode.

Expected Result

After applying one of the recommended solutions:

• The overDueDays field is sorted numerically rather than alphabetically.
• Ascending sorting displays values in the correct numeric sequence.
• Descending sorting displays values in the reverse numeric sequence.
• Sorting behaves consistently in both Designer and Viewer modes.
• Grid widgets correctly recognize the field as a** numeric value**.

Notes

• Measure formatting (Number, Currency, Decimal Places) does not affect sorting behavior.
• Grid sorting is based on the underlying data type.
• Functions such as REGEXP_SUBSTR() typically return string values.
• Regex-based calculated fields should be converted using toNumber() when numeric sorting is required.
• Database-side conversion using **CAST() **is the preferred approach whenever possible.
• Helper fields and advanced sorting options are useful when display formatting must remain unchanged.

Conclusion

If a calculated field such as overDueDays is sorting incorrectly in a Grid widget, the field is most likely being treated as a string rather than a number.
Converting the value to a numeric data type, either in the data source or through a calculated field, will ensure correct numeric sorting and consistent behaviour across Grid widgets.

Related Articles

• How To Sort Grid Widget Based on a Specific Field?
• How do I sort the measure field based on hidden column in a Grid widget?
• Advanced Sorting Options (Bold BI Documentation)