Correctly Handling Decimal Division in Bold BI - Tips & Methods

When utilizing the Expression Designer in Bold BI, particularly with data sources in extract mode, it’s important to ensure that division operations return accurate decimal values. Users may notice that expected decimal results are not displayed; for instance, the result of 1 divided by 2 may appear as 0 instead of 0.5, or 100 divided by 80 might show as 1 when 1.25 is expected.

Understanding the Issue

The root of this problem lies in the data type of the values involved in the division operation. If the values are of the INT (integer) data type, the result will also be an INT, which does not include any decimal places. This behavior is typical in database systems such as MSSQL and PostgreSQL when performing arithmetic operations with INT values.

Resolving the Issue

To obtain the correct decimal results, the values must be cast to the DECIMAL data type before the division operation is performed. This ensures that the database processes the operation with decimal precision, thus returning a result with the necessary decimal places.

Here are two methods to achieve the correct casting:

1. Using the CAST Function:

   CAST(value AS DECIMAL) / CAST(other_value AS DECIMAL)
   

   For example:

   CAST(1 AS DECIMAL) / 2
   

2. Using Decimal Values Directly:
   Simply include a decimal point in the operation to indicate that the values are DECIMALs.
   For example:

   1.0 / 2
   

By applying either of these methods, the Expression Designer will correctly handle division operations and return the expected decimal values.