KNOWLEDGE BASE

Removing a Top And Bottom Percentage of a Field's Values (Outliers) When Aggregating


Published: 17 Apr 2017
Last Modified Date: 17 Apr 2017

Question

How to remove the top and bottom X% of a field's values (outliers) when aggregating

Environment

Tableau Desktop

Answer

Step 1: Create the view

  1. Open Tableau Desktop and connect to the Superstore sample data source.
  2. Drag City to Rows.
  3. Drag Number of Records to Text on the Marks card.
  4. Sort City descending accroding to sum of Number of Records.

Step 2: Create the two needed parameters

  1. In the Data pane, click the drop-down arrow in the upper right corner and select Create Parameter.
  2. In the Create Parameter dialog box, do the following and then click OK:
  • Give the field a Name, for example: Bottom Pct.
  • For Data type, select Float.
  • For Display format, select Percentage.
  • For Allowable values, select Range.
  • Set Minimum to 0.05, Maximum to 0.25 and Step size to 0.05.
  • Right-click Bottom Pct. in the Parameter pane of the Data window and select Show Parameter Control.
  1. Repeat the above steps to create another parameter Top Pct. with Minimum to 0.75, Maximum to 1.0 and Step size to 0.05.

Step 3: Create a calculated field that only includes the middle Y% of values.

  1. Choose Analysis > Create Calculated Field.
  2. In the Calculated field dialog box, do the following and then click OK:
  • Name the calculated field, for example, Filtered Sales.
  • Copy and paste the following statements to the formula field:
IF [Sales] > { FIXED [City]: PERCENTILE([Sales], [Bottom Pct.])} AND [Sales] < { FIXED [City]: PERCENTILE([Sales], [Top Pct.])}
THEN [Sales]
END

Step 4: Create a filter that can exclude the bottom/top X% of values of the data.

  1. Drag the calculated field Filtered Sales created above to the Filters shelf.
  2. In the Filter Filed dialog box, select All values and click Next.
  3. Select Special > Non-null values and click OK

Additional Information

Two parameters are needed because the argument 2 of PERCENTILE() must be a float literal, which means that we cannot simply use the expression “1 – [Bottom Pct.]” as the second argument of this function in the calculated field, because it has an operator inside and thus is not a float literal. 
Did this article resolve the issue?