Fixed Level Automatic Bin Sizes - LoD Edition.

Version 2

    Description:


    Tableau V9.1 suggests the Optimal Bin Size by performing the following Calculation on the dataset.

     

    ( MAX([Measure Value]) - MIN([Measure Value]) )

    /

    3 + LOG(COUNTD([Measure Value], 2) + LOG(COUNTD([Measure Value]))

     

    Create Discrete Bins from a Continuous Measure

     

    But Tableau also Rounds the Bin Size to the nearest Base 10, so $1,864 will Round to $2,000.

     

    So I thought, why not create this record level with LoDs so that I can use Bins in an LoD (This method will probably go away once Tableau V9.2 is released).


    Inputs and Setup: (for table calculations, delete if not applicable)

     

    [MAX Sales] => INT(MAX([Sales]))

    [MIN Sales] => INT(MIN([Sales]))

     

    [Difference in Max/Min Sales] => [MAX Sales]  - [MIN Sales]

     

    [Number of Bins] => INT(3 + LOG(COUNTD([Sales]), 2) * LOG(COUNTD([Sales])))

     

    [Optimal Bin Size] =>

     

    INT({

        FLOOR(INT([Difference Max and Min Sales] / [Number of Bins] )

              /

         POWER(10, FLOOR(LOG(INT([Difference Max and Min Sales] / [Number of Bins] )))))

    *

    POWER(10, FLOOR(LOG(INT([Difference Max and Min Sales] / [Number of Bins] ))))})

     

    [Sales (Bin)] =>

     

    FLOOR([Sales] / [Optimal Bin Size]) * [Optimal Bin Size]

     

    WHY DO THIS INSTEAD OF JUST USING A NORMAL BIN? Because with one slight adjustment, we can change the level the BINS are calculating, without needing to re-create the bins as new data pours in.

     

    INT({ FIXED [Sub-Category] :

        FLOOR(INT([Difference Max and Min Sales] / [Number of Bins] ) / POWER(10, FLOOR(LOG(INT([Difference Max and Min Sales] / [Number of Bins] )))))

        *

        POWER(10, FLOOR(LOG(INT([Difference Max and Min Sales] / [Number of Bins] ))))})

     


    Screenshot:


    11-16-2015 8-06-18 AM.png

     

    11-16-2015 8-08-24 AM.png


    This could be coupled with Human Readable Row Level Dynamic Bins to create Automated Human Readable Bins simply by swapping the Parameter Bin Size with the Optimal Bin Size calc shown above.


    Tableau Version:

    9.0


    Original Author:

    Rody Zakovich