Writing formulas in Tableau

The formula editor has built-in coloring and validation to help you avoid syntax errors. As you write the formula, syntax errors are underlined with a red squiggly line. Hover over the error to see directions for fixing it. Also any errors with the calculation are shown in a drop-down list. When the calculation is valid, a green check mark is displayed.

When you are writing formulas, any part that displays in bold indicates that it will be computed locally within Tableau on the aggregated results. Any normal weight text will be computed at the database level.

Formulas are made up of the following parts:

  1. Functions

    The Functions area of the dialog box contains all the functions you can use to create a formula. The functions are organized into categories, which are available from the drop-down menu. By default all functions are displayed.

    You can display a brief description for each function by clicking its name in the list box. Double-click a function to include it in a formula. Functions are colored black in the formula.

  2. Fields

    All data source fields and calculated fields are listed in the Fields area of the dialog box. Binned fields and sets are not listed because they cannot be used in calculations.

    The field’s data type and the name display in the list. Use the drop-down menu to select a secondary data source and see its fields.

    Double-click a field name to include it in a formula. You can also just type the bare field name. However, if the field name includes special characters such as spaces, it must be delimited with square brackets as in SUM([Store Profit]). A right bracket (]) can be doubled to include it in the field name itself. For example, the field name “Store Profit]” would be written as [Store Profit]]].

    Fields are colored orange in the formula.

  3. Operators

    Operators are not available on the dialog box like functions and fields. Instead, you must manually type the operators into your formula. All standard operators such as addition (+), subtraction (–), multiplication (*), and division (/) are supported. Operators are colored black in the formula.

  4. Parameters (optional)

    Parameters are placeholders variables that can be inserted into calculations to replace constant values. When a parameter is used in a calculation, you can then use a parameter control to dynamically change the value. Parameters are colored purple in the formula.

  5. Comments (optional)

    You can insert custom comments for your calculations as a means of annotation for later review. To add a comment to a calculation type two forward slash characters into the formula pane.

    For example:

    Sales * Profit //John’s calculation

    In this example //John’s calculation is a comment.

    A comment starts at the two forward slashes (//) and goes to the end of the line. A multiline comment can be written by starting each line with two forward slashes (//). Comments are colored green in the formula.