Getting Started
Introduction Input Data Designer Interface
Data Preparation
Selecting Data Data Type Conversion Cleaning Data Exercises
Filters & Joins
Documentation Filters Join Types Inner Join Outer Joins
Formulas & Aggregations
Formulas Aggregating Data
Sort & Output
Sorting Data Output Data
Other Tutorials & Content
Learn Python Python Data Science Reference Blog

Alteryx Formulas

Once we have imported data into our workflows we will often want to create additional columns of calculated values based on the data from our original sources or modify the values in existing columns. In Alteryx we can use the Formula Tool to do this using basic arithmetic functions along with more advanced functions provided within the tool.

Alteryx Formula Tool

Alteryx Formula Tool

Within the Formula Tool configuration window you can create new columns by clicking on the “Select Column” dropdown menu and selecting “New Column”. You will then be presented with a text box to type in the column name. You can also modify existing columns by clicking on the required column name.

Alteryx Select Column

When creating a new column you have the option to define the data type for the column by selecting the data type from the drop down menu along with precision. If modifying the values in an existing column then the data type cannot be changed.

Alteryx Formula Data Type

Alteryx Expression Editor

Within the expression editor you can type all of your formulas manually.

Alteryx Expression Editor

To use any existing column in the formula, either type the column name inside brackets i.e. [Sales] or click on the “X” icon and select from the column list.

Alteryx Existing Columns

You can then use the following syntax to perform basic arithmetic operations between the existing columns:

  • + Addition
  • - Subtraction
  • * Multiplication
  • / Division

Formula Tool Built-in Functions

If you are confident with using more advanced functions, you can click on the f(x) icon and insert any Alteryx built-in function that can be modified to your needs. Alteryx provides a large collection of functions to choose from and more details can be found on these in the Alteryx reference documentation here. These can also be typed manually once you are familiar with the function syntax.

Alteryx Formula Functions

Finally to create or modify another column, simply click on the “+” icon or the bin icon to delete the column.

Alteryx Additional Column

Let’s see how we can use this tool in practice. In our workflow we have the retail price of the products bought and any promotion discount rate that was applied. From this we can use the Formula Tool to calculate the sales value for each order. Let’s do this now.

Step 1:

Drag a Formula Tool from tool pallet to the workspace and connect to the input to the output of the Data Cleansing Tool.

Alteryx Add Formula Tool

Step 2:

In the configuration window for the Formula Tool select “Add Column” from the “Select Column” dropdown. Name it “sales_value” and type the expression in the expression editor as below or construct it using the “Existing Columns” list.

Alteryx Formula Tool Configuration Window

Step 3:

Finally let’s run the workflow. You should see the “sales_value” field appear in the output of the Formula Tool.

Alteryx Formula Tool Output