KNOWLEDGE BASE

Filtering Just a Selection of the Data in the View


Published: 10 Feb 2018
Last Modified Date: 22 Feb 2018

Question

How to filter only a subset of the data in the view.

Environment

Tableau Desktop

Answer

As an example, using Superstore as the data source we can see that January 2015 had negative profits. What if we wanted to filter out the larger discounts for that month and see if the profits would return more in line with the rest of the year. In that case we would want to be able to adjust the filter for discounts and see how it affect just that month without affecting the rest of the data, so we can compare it.

This can be accomplished using a parameter for the date selection and calculated fields for the dimensions or measures you want to filter. In our example we are concerned with viewing a month at a time and we specifically want to filter the sub-category.

Our data source is the orders table from the Sample – Superstore excel file, the workbook Selective Filtering.twbx can be downloaded from this article. 
 
CLICK TO EXPAND STEPS
Step 1 - Create a parameter
  1. Right-click anywhere in the Data pane of Tableau Desktop and select Create Parameter...
  2. Fill in the different fields as described below: 
    1. Name: Month Select
    2. Data type: Date
    3. Display Format: <month>, <Year>
    4. Allowable values: Range
    5. Set from Field -> Order Date
    6. Step Size: Checked and set to 1 Months
CLICK TO EXPAND STEPS
Step 2 - Create three calculated fields
Next, we are going to create a calculated field to use to highlight the month that is selected.
  1. Select Analysis > Create calculated field...
  2. Name the calculated field "Selected Month"
  3. Enter the following formula and click OK
    IF DATETRUNC('month',[Order Date]) = DATETRUNC('month', [Month Select]) THEN TRUE
    ELSE FALSE
    END
Next, we create the calculated field for the Sub-Category.
  1. Select Analysis > Create calculated field
  2. Name the calculated field "Sub-Category of Selected Month"
  3. Enter the following formula and click OK
    IF [Selected Month] THEN [Sub-Category]
    ELSE NULL
    END
    • What this calculation does is compare the month from our parameter to the month for each records order date. If it matches then we leave the Sub-Category, if it doesn’t we make the value Null. This means only the records from the month we select with have a Sub-Category recorded, and all the rest of the records will be null and won’t be affected when we filter on this field.
Then we create the calculated field for the Discount.
  1. Select Analysis > Create calculated field
  2. Name the calculated field "Discount for Selected Month"
  3. Enter the following formula and click OK: 
    IF [Selected Month] THEN [Discount]
    ELSE NULL
    END
We will need to change the default property number format for this new calculated field to percentage so that it looks right when we at it to the view Next, we create the calculated field for the Sub-Category.
 
CLICK TO EXPAND STEPS
Step 3 - Build the view
Now we build the view to use all of this:
  1. Right click and drag [Order Date] to the columns shelf, selecting MONTH(Order Date) from the Menu.
  2. Drag [Profit] to the Rows Shelf.
  3. Change the Mark Type from Automatic to Bar
  4. Right click on [Month Select] parameter and select Show Parameter Control.
    • Ensure it is set to slider control.
  5. Drag and drop [Selected Month] onto Color.
    • Edit the colors to your preferences.
    • At this point you should be able to change the parameter and see the selected month highlighted with color so you know what month you are affecting.
  6. Drag and Drop [Sub-Category for Selected Month] on the Filter Card. Make sure all values are selected.
  7. Right click on the [Sub-Category for Selected Month] pill in the filter card and select Show Filter.
    1. Ensure that multiple values can be selected as we need Null to always be selected or we will filter all data outside of the selected month.
The view is now built and you can explore the data. Select a month using the parameter and then uncheck some of the sub-categories and notice that is only affect the data in the selected month.
Did this article resolve the issue?