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

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 :-)

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

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

Thanks Santiago,

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

Cheers