The Calculate formula step calculates the value of a cell using a formula. Formulas can return a value of any type. Formulas can reference other columns using
$Column_Name syntax and many built-in functions.
Learn the basics of writing formulas
Visit Formulas and calculations to learn how to work with formulas.
Formula: required, the formula to apply to create the target column values. Formula can return a value of any type. Formulas can reference other columns using
$Column_Namesyntax and many built-in functions.
Target column: required, configure the column which will receive the output. Output type will be determined automatically depending on the output type of the formula.
Write result to the current column: overwrite the input column with the result.
Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed right after the input column.
Calculate the late fees for invoices. Late fees are derived from invoice due date and when it was paid. Late fees are added to invoices which are paid more than 15 days after their due date. Late fee is then 0.5% of the amount for each date the invoice is not paid past the due date.
Load the invoices data set. it has two columns -
Paid date- which provide information about invoice payments.
Paid dateis empty if the invoice was not yet paid. You data may look like this:
You can use dateDiff function in Calculate formula step to calculate how many days are between two dates.
Once you add the step, you will get output that can look like the following screenshot. Notice the error cells highlighted in red - they show rows where invoices have not yet been paid so it is not possible to calculate the number of days. We’ll clean this in next steps.
To clean the errors from previous step, use Clear error cells step. This step will remove the errors and use
null(No value) instead. You will get an output like this:
To calculate the late fees, we can use formula like this:
if($Paid_after_days > 15, 0.005 * $Invoice_amount * ($Paid_after_days - 15), 0)
The formula uses
iffunction to only run when
Paid after daysis greater than 15. If invoice is paid in 15 days or less, we do not add any fees. Otherwise we calculate the fee as 0.5% of the
Invoice amountfor each day the payment is late.
The output will look like this. Notice again that we have errors in the output - this time caused by No value cells in the
Paid_after_dayscolumn. No value cells cannot be used in calculation and hence an error is raised. We can solve this in two ways:
Improve our formula from the previous step with more complex condition to not calculate anything for those rows. This can be done for example like this:
if($Paid_after_days == null, 0, if($Paid_after_days > 15, 0.005 * $Invoice_amount * ($Paid_after_days - 15), 0 ) )
Use Replace errors step to clean the result after the calculation. This is the approach we’ll use here.
Finally, we can round the output to 2 decimal places to make it nicer. Do not forget to set the display format for the column to get nicer output.
The target column data type is automatically determined based on the type returned by the formula. If the target column is of a different type, the type of the target column will be changed to match the formula’s return type.