To round to exactly two decimals the first thought is to use ROUND or FLOOR. Right? However, Tableau rounds numbers based on the original value in the database, following the “round up by half” convention. It may then return less decimal positions than you are hoping for. In Desktop the format option is there to save your day, but what about Prep?
The two decimal case
The case was proposed to me by a contact that needed specifically two decimal position from the workflow output (‘decimal digits’). For example:
23.345678654 should be 23.35
23.000000001 should be 23.00
23.100034563 should be 23.10
The solution
Using Calculated Fields with both ROUND and FLOOR return results that are not meeting the criteria of two decimal positions. ROUND([input],2): 23.35, 23 and 23.1, while FLOOR([Input]*10^2) / 10^2 returns similar (note rounding) unsatisfactory results. It does not return similar results as the SQL SELECT FORMAT (23.000000001, ‘N2’). How then to resolve this?
A solution that works involves splitting and rebuilding your numbers as Strings. A caveat hers is that the output is acceptable as String (Abc).
Split the full number in two using the decimal point. This way we can handle characters before and after the delimiter (the decimal annotation). We keep the integer with SPLIT and take the decimals with LEFT SPLIT. Then, optionally, use ROUND(input,2) to round up by half, and add the required number of decimals to force, here two (“00”,2).
The calculated field is as follows:
SPLIT(STR([Input]),".",1) + "." + LEFT(SPLIT(STR(ROUND([Input],2)),".",2)+"00",2)
Want to use the decimal comma, then replace every “.”, with “,”.
Did you find another solution? Share your thoughts in the comments section below.
If you want to read other blogs about software like Tableau and Alteryx, follow a training course or book a consultant please visit The Information Lab NL for more information.