2 Replies Latest reply on Jan 24, 2017 5:06 AM by Christian Schwehm

    Fixing the number of bins and excluding outliers in Tableau 10

    Christian Schwehm

      Hi all,

       

      I really like tableau, but I just do not succeed in two things:

       

      1. I want to have a parameter that defines the number of bins Tableau should use in a histogram, I just get the option to select the size of each bin. Is there a way to get a slider to select the number of bins?

      2. Is it possible to have a parameter to exclude the top and bottom x percent of data from the histogram, so that you do not have to bother to much about outliers?

       

      A Workbook is attached :-)

       

      Thanks for your help !!

        • 1. Re: Fixing the number of bins and excluding outliers in Tableau 10
          Santiago Sanchez

          Hi Christian,

           

          This is quite an interesting challenge! I think I only have a partial solution for you, unfortunately. Looking forward to hear other alternatives from the community.

           

          Let's start with question #2. You can create a calculated field to 'Exclude Outliers' with this formula that indicates we want to look individual records over or under the top/bottom percentiles of the whole data set:

           

          [Werte Faktor 1] >= { PERCENTILE([Werte Faktor 1], [Exclude bottom x percent]) }

          AND

          [Werte Faktor 1] <= { PERCENTILE([Werte Faktor 1], [Exclude top x percent]) }

           

          Now you can drag this new calculated field to filters and select TRUE. For the second part of the answer to work, you'll want to add it to context:

           

           

          Note we are using an LOD ({ }) expression to get the percentiles. For more information about them I suggest looking at Overview: Level of Detail Expressions or Understanding Level of Detail (LOD) Expressions | Tableau Software

           

          For question #1, we need a couple of calculated fields. First, we need to determine the bin size, which we can do by writing 'Custom Bin Size':

           

          ( { MAX([Werte Faktor 1]) } - { MIN([Werte Faktor 1]) } ) / [Bins]

           

          With the size defined, we can now create bins. Here's where I can only give you a partial answer as you'll see in the 'Custom Bin (Static)' calculated field:

           

          IF [Werte Faktor 1] <= { MIN([Werte Faktor 1]) } + [Custom Bin Size]

          THEN { MIN([Werte Faktor 1]) } + [Custom Bin Size]

          ELSEIF [Werte Faktor 1] <= { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 2)

          THEN { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 2)

          ELSEIF [Werte Faktor 1] <= { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 3)

          THEN { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 3)

          ELSEIF [Werte Faktor 1] <= { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 4)

          THEN { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 4)

          ELSEIF [Werte Faktor 1] <= { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 5)

          THEN { MIN([Werte Faktor 1]) } + ([Custom Bin Size] * 5)

          END

           

          This formula creates 5 bins, using the custom bin size which is based on a parameter; however, we need to write a condition for every bin, which makes this not fully dynamic. If you know most times you'll need a certain number of bins, say 10, then you could write those 10 conditions and limit the selections available in your parameter; but I know this might not be ideal.

           

          Hope this helps! A workbook is attached.

          • 2. Re: Fixing the number of bins and excluding outliers in Tableau 10
            Christian Schwehm

            Thanks Santiago,

             

            that's a good start and gave me some ideas to explore it further :-)

             

            Cheers