Minus Values Are Included Into the Bin Of "0" When Using MS SQL Server

KNOWLEDGE BASE

Minus Values Are Included Into the Bin Of "0" When Using MS SQL Server

Published: 03 Apr 2018
Last Modified Date: 24 Apr 2018

Issue

After creating bins, you notice that the bin labeled as "0K" contains negative values (for example, values could include between "-10000K" or more, and less than "0K").

Environment

Tableau Desktop
MS SQL Server

Resolution

Option 1

Convert the field being binned from integer to float before creating the bin.

Option 2

Use a calculated field to create bins.

FLOOR([VAL]/[Bin Size])*[Bin Size]

Cause

Bins are computed by using the FLOOR function in SQL but if the value is an integer field, the bin size also happens to be an integer. When you divide two fields that are integers in SQL, it will truncate the decimal part of the result.
For example, the negative values near zero, we get -14/15 = TRUNC(-14/15) = 0. That is resulting in the behavior.