Using Tableau Mulitple Select Parameter Within Calculated Fields

Tableau mulitple select parameter gives the end user the ability to control many elements of their dashboard. Many use cases employ parameters to switch between measures, determine sort order or, control other aspects of how data is displayed. Parameters can also be made useful in analysis by including them as an input to a calculated field. In this post, I’ll show you how to add parameter control within a calculated field to extend the estimated end dates of projects and determine which would be most affected by anticipated delays.

This data set contains start dates, estimated end dates, and project deadlines for a client company’s construction projects. The project manager wants to be able to determine how prone sensitive projects are to delays to help allocate resources to where they are most needed. Our proposed solution would allow the manager to extend project end dates by some percentage of total project length and immediately see where such extensions would cause projects to miss their deadlines. Here’s how to get started:

1. Drag Project Start Date to columns.

a. Right-click on the Project Start Date pill you just placed and select continuous exact date from the context menu.

2. Add My Project Names to rows.

Your view should now look like this:

3. Create Estimated End Date Adjuster parameter.

a. Click the drop down in the data pane next to dimensions and select Create Parameter.
b. Select data type as Float.
c. Set the current value to 0.
d. Change the display format to Number (custom) with no decimals and a % suffix.

Your parameter options should now look like this:

The parameter will not do anything if it is not exposed to the end user for input. To expose the parameter:

4. Right-click the parameter pill you just created and click “Show Parameter Control.”

Now we will need a calculation that uses this parameter. We want the user to be able to increase each project end date by a desired percentage. To achieve this, we will need to use a nested calculation or a calculation within a calculation. Let’s create the first required calculation.

5. Create a new calculated field.

a. In the calculation window, type (or paste) the following function:
        INT([Project Location]*[Estimated End Date Adjuster]/100000)

b. Name the calculated field, “Project Extension” and click OK.

This calculated field will take each Project and increase it by the percentage selected by the user in the parameter controls. The reason we are dividing by 100000 is so that the newly increased project end date is more realistic. To allow this calculation to modify the view, we will create our second calculation using the DATEADD function.

6. Create a new calculated field for Project End Date

a. In the calculation window, type (or paste) the following function:
        DATE(DATEADD(‘day’,[Project Extension],[Estimated End Date]))
    b. Name the calculated field New Target Date and click OK.

7. Drag New Target Date to columns to the right of Project Start Date.

a. Right-click New Target Date and select continuous exact date from the context menu.

8. Create a dual axis chart.

a. Right-click New Target Date and select dual axis.
b. Right-click one of the axis that contains dates and select synchronize axis.
c. Right-click each axis that contains dates and select show header.

9. Format the view and labels.

Note: The marks card now has three sections: All, Project Start Date, and New Target Date.

a. Underneath the Project Start Date section of the marks card, drag Estimated Project Length to size.
b. Drag Project Start Date to label. Click the label mark and set alignment to left.
c. Underneath the New Target Date marks card, drag New Target Date to label.
d. Click the label mark and set alignment to right.
e. Remove Measure Names from color, and choose two different colors to distinguish from the bars and Gantts.

The bars represent the project start date up to today, while the Gantt bars represent the target end date. Your view should look like this:

Now we can adjust our Target Dates using our parameter. It would be ideal to format the colors to highlight projects that have the potential to go over their respective deadlines.

10. Create a calculated field for Goal Date.

a. In the calculation window type in, or copy and paste the following:
          [Project Start Date] + [Estimated Project Length]

11. Create a calculated field for Projects At Risk.

a. In the calculation window type in, or copy and paste the following:
          If [End Date] < [New Target Date] then ‘On Track’ else ‘At Risk’ END
      b. Drag “Projects At Risk” to color under the Project Start Date marks card.

Your view should appear as the following:

When projects are at risk of not meeting their deadlines, they now appear as a different color.

12. Using the Tableau multiple select parameter controls type in a new value and watch as the new target dates will change in value!

When the Project Manager adjusts the parameter controls, projects that are no longer at risk will change colors accordingly. I performed additional formatting and changed colors to truly demonstrate the parameter in action.

Parameters within calculations are a powerful tool and can be used in many types of cases. I challenge you to attempt to use a parameter within a moving average table calculation next!

Want to take your Tableau projects to the next level? Learn more about how Tableau analytics experts can help grow your skills today.