How to Create Calculated Values using Dialog Box in Tableau

Using the Calculation Dialog Box to Create Calculated Values

You can modify and compare table calculations.

Calculated Values require entering of fields, functions and operators. Tableau strives to make formula creation fast and easy, so it is possible to write formulas with minimal typing. Once you’ve connected to a data source, you can create a calculated field from the main menu by selecting Analysis/ Create Calculated Field. This example uses the Superstore spreadsheet. Below diagram shows the Calculated Value editing window.          

The figure shows a calculation for Profit Ratio that uses two fields from the Superstore file to derive the result. The Name field at the top of below diagram is where you type the name of your Calculated Value as you want it to appear in the data window of the worksheet. The Formula box is used to write the script for the formula.

You will also see that Tableau color which encodes different elements of formulas so that they are easy to separate visually. Fields are orange, the Parameters are purple, and Functions are blue. Notice the example in below diagram includes comments at the top, color-encoded in green. Comments are useful for documenting sections of complex formulas or for adding basic descriptive information to other analysts that may use your formula in their work. You can add comments anywhere in the formula window by typing two forward slashes (//) in front of the text.

Diagram of Formula dialog box or editing window

Below the formula window is a green check mark followed by the statement “the calculation is valid”. This is the formula editor that will you help you correct syntax errors. If you get something wrong a red X will appear in below diagram, you see this in action.

The real-time formula editor Diagram

For example, if the beginning parenthesis is omitted in front of the sales field, clicking on the error message —or in the formula near the crooked red line— will provide more information about the syntax error. Typing in the missing parenthesis will correct the problem. If you are new to writing formulas, or if you are creating a particularly complex formula, Tableau’s editor will help you find and correct errors.

Referring to below diagram again, you can see four panes on the bottom half of the window. These panes display the available fields, parameters, and functions. If you have a particular field or function selected, the yellow window at the far right provides a brief description of the field or the formula definition.

Checkout Tableau Interview Questions

Field selection:

Looking below the fields title, you will see a filter that allows you to select different data sources (if you have more than one being used in your worksheet), or filter for specific data types available (numbers, text, dates, etc.). Below diagram shows this in action with the number data types only being displayed below.

Diagram of Filtering field selections for numbers

If you have many fields in your source data, a high-level field selection filter may not prune the list enough. In the below diagram, notice the small boxes below each window that provide a fuzzy string search for a specific field name. Notice that the parameter and Function windows also provide the same search capability.

You can add fields to your Formula window by typing them manually, pasting them in from a text editor, or by double-clicking on the desired field from the Fields window. If you are new to writing formulas, use the double -click method. Tableau inserts the appropriate syntax automatically. For example, double-clicking on the Profit field in the Fields window will cause the following script to be entered: ([ Profit]).

Checkout Tableau Server Tutorials

Function Selection:

Functions can be added exactly the same way. The filter at the top of the Functions window lets you filter for a function category. To add functions without typing, place your cursor within the location of the formula window where you want the function to be placed and double-click on the desired function name in the function window below. Below diagram shows the function window.

Filter the function window Diagram

When the Sum function is selected, the yellow help window displays a brief description of the function along with the function syntax. If you want a more detailed definition, selecting the help menu option will take you to Tableau Software’s online manual.

Parameter Selection:

Parameters are useful when you want to add interactivity and flexibility to your report, or to experiment with what-if scenarios. The parameters are a single select drop down. To select multiple items from this list you need to build a calculation based on multiple selection. 

They are optional elements which allow you to add variables in formulas. Below diagram shows two parameters that are included with the Superstore sample file. Parameters will be covered in detail in the sections on parameters at the end of this chapter.

When you complete editing the formula, don’t forget to click the OK button at the bottom because the new field isn’t created until you do that. If you get interrupted while writing a very long formula either keep your window open, or copy the script to a text editor and save it. When you resume work, you can paste that script back into the formula window and continue. Once you get comfortable with the formula editor and the available functions, you’ll find many ways to leverage Calculated Values.

List Of Tableau Courses:

Tableau Advanced

Tableau Server

Data Visualization and Dashboarding Fundamentals