Reference: Calculated Fields in Reports

Calculated fields use dataset fields to create additional points from your existing data.

A calculated field can either:

Perform a calculation on database fields to create a value that isn't directly stored in VNDLY.

Select values in database fields based on customized criteria.

A few of the reasons why you might want to use calculated fields include:

The metrics you need aren’t directly stored in VNDLY.

You want to examine data across multiple dataset fields in your report.

You want to filter or aggregate data.

These formulas are available in VNDLY for calculated fields:

Formula	Description	Example Use Case
Add	Add up multiple numeric fields. Displays total of fields combined into column format. You can add up to 5 numeric fields.	You need to display a combined value of the Client VMS Amount and Client Amount fields.
Age	Number of days since a date. Note that only 1 age value can be used per calculated field.	Display the number of calendar days since an approved value was logged. Example: Age of current date - approved date = 18 days.
Array To String	Formats an array into a string of comma-separated values.
Avg	Averages the values.	You need the average bill rate for a resource manager. Average client rate.
Case	Transform values based on conditional statements. Operates as an if/and statement. Typically used to populate values if a client needs something specific for a business unit, hiring manager, and so on. You can use additional if/then statements within case statements.	If the hiring manager equals Ashley Smith, you want the value marked as "Case Testing", if it doesn’t equal Ashley Smith, leave blank.
Coalesce	Returns from a group of fields the value of the first field that isn't null.
Concatenate	Concatenate, or combine, multiple string-type fields into 1 field. This option can be used up to 7 times in 1 formula. Defaults to adding a space as the separator.	You need to combine invoice number with the vendor name, this calculated field would display both values combined into a single field.
Count	Count occurrences of the selected field value in a selected group of fields.	You need a count of how many system IDs there are in each source type.
Date Diff	Date difference in days.	Displays the different between invoice creation date and approved date.
Date Trunc	Truncate a date field to a specified level of precision.	You only need the month from the date field.
Divide	Divide 2 numeric fields. This operates similarly to Sum.	You need to obtain the value of the client amount divided by the MSP tax amount.
Min	Minimum value in the group of values.	You want to display the earliest date of timesheet values, so the min calculated field removes the timesheets that aren't the smallest values.
Max	Maximum value in a group of values.	You want to display the latest date of timesheet values, so the max calculated field removes the timesheets that aren't the largest values.
Multiply	Multiply values of selected fields. This option operates opposite of Divide.	Display an increased bill rate comparison by creating a separate Value field with the suggested increase. Example: a 20% increase, so the Value would be an integer of 1.5. Then use this formula to multiple the source of the created value against the source of the bill rate.
Pad Text	Add padding to the text of the values selected. Operates like SubString formulas.
Round	Rounds a value to its nearest integer or number with closest number of decimal places. Precision can also be a negative.	You need the number to round by 1 precision, which then displays the rounded number after decimal.
Subtract	Subtracts 2 numeric fields. Displays total of fields subtracted in a single column format. You can subtract up to 5 numeric fields.	You need to display a subtracted value of the Client Amount from the Client VMS Amount field.
SubString	Sequence of characters in a string extracted from the left, middle, or right of a specified value.	You need the first 3 letters of the Approved By person.
Sum	Aggregate function to sum values of a field in the group.
Date To String	Formats the input expression as a string, converting a value into text.
Un Nest	Splits multiple values in a field into multiple rows.

These data types are available in VNDLY:

Data Type	Description
Integer	Returns a whole number, no decimals.
Number	Returns a number, including decimals.
String	Returns a series of characters.
Date	Returns a date. Example: 15 Jun 2021
Date and Time	Returns a date and time. Example: 15 Jun 2021 05:38:07 PM