In this video, you’ll learn what the SUB, SUM, DIV, and PROD expressions do and how to create a SUB data expression in a calculated field.
The SUB, SUM, DIV, and PROD expressions allow you to use basic arithmetic in conjunction with data from Workfront to create new data. For example, as a program or portfolio manager, you want to know the profit from each project. By subtracting the actual cost field from the actual revenue field, you can determine if the profit on your projects is positive or negative.
Now, you can do that manually, or you could have the system do it for you automatically using the SUB data expression. In a calculated field, mathematical expressions include the name of the expression and two data points, either a field in Workfront or a number, depending on the type of information needed. Let’s create a profit calculated field using the SUB data expression.
Select Calculated in the field option in your custom form.
Fill in the Label instructions, if needed, and the Format fields. Note with mathematical data expressions, you should always use either Number or Currency for the format. In this example, use Currency. Below, you’ll see a box titled Calculation where you’ll build the calculation for your field. If you already know how to build expressions, you can start typing it into the calculation field.
If not, you’ll find two search boxes titled EXPRESSIONS and FIELDS that allow you to search for the expression types and fields you want to use in your calculation. We’re going to use the search boxes. In the EXPRESSIONS search box, start to type the SUBTRACTION expression. When it appears in the list, click on it to put it in the calculation box.
Next, you’ll need to enter the data points needed for the expression.
In this example, actual revenue and actual cost.
Go to the FIELDS search box and start typing the name of the field.
When it appears in the list, click on it to put it in the calculation box.
You’ll notice the system automatically starts adding the necessary formatting to make the calculation work in Workfront.
If this were the sole data point you needed for the expression, you’d save it and move forward. However, there is another data point needed for this calculation. We need to subtract the actual cost from the actual revenue. So, once again, go to the FIELD search box, type in the field, click on it when it appears, and the system puts it into the equation. The only thing you’ll need to add is a comma between the two data points.
For more information on calculation structure, see the “Learn the data expression structure” tutorial.
Save the field and the custom form.
At this point, the calculated field can be used in different ways, like a view or a custom report, to see if the project is profit-positive, neutral, or negative.
Simple mathematical expressions can be built using well-known mathematical science, like plus, minus, divide, and times. For instance, in the SUB example, it was initially written with the SUB expression and the comma to represent the minus sign. However, it can also be written with parentheses and the minus sign.
As expressions become more complex and you begin to nest expressions within expressions, it may be easier to read, understand, and keep track of what’s going on within those expressions that include mathematical symbols. For example, you want to know when a task should have been completed based on when the task status moved to In Progress, which triggered an Actual Start Date, and what was the intended length of the work, also known as the plan duration. Now the projected date won’t work because it can move if the task is late, and the planned completion date doesn’t help if there are delays in prior tasks. So to get an accurate number, you can create an expression using the ADDDAYS date and time expression as well as the DIVISION mathematical expression.
It can be written either like this or like this. As a note, the duration field is stored in minutes.
So to show the time in days, it has to be divided by 480 minutes.
Whichever way you choose to write a mathematical expression, you can create simple or complex equations with an expression to produce the information you’re looking for within Workfront. -
The ROUND expression takes any number and rounds it to a certain number of decimal places.
Most of the time, the ROUND data expression is used in conjunction with another data expression and when the format field is left as either Text or Number.
Let’s create a calculated field to determine the difference between the number of hours planned and actually logged on a task, which will require the SUB expression and look like this:
And since time is tracked in minutes and the preferred format is to show the information in hours, the expression also needs to be divided by 60 and look like this:
If the format is changed to Number when building the calculated field in the custom form, you can change the number format when adding the field in a view.
However, if the field format when creating a custom field is left as Text, the format can’t be easily changed within the view. The ROUND expression has to be used to avoid seeing numbers like this in your project:
The ROUND expression includes the name of the expression (ROUND) and, typically, two data points. These data points can be an expression or a field in Workfront, followed by a number to indicate how many decimal places you would like to go.
In the expression calculating the difference between planned and actual hours, use this expression —DIV(SUB({workRequired},{actualWorkRequired}),60)—as the first data point. Then make sure whatever number comes from that expression doesn’t go more than 2 places to the right of the decimal.
The expression could be written like this: ROUND(DIV(SUB({workRequired},{actualWorkRequired}),60),2).
