Create calculation and rollup properties

You can create properties that populate data based on calculations from other properties. With rollup properties, you can automatically calculate the min, max, count, sum, or average values for properties on associated records. With calculation properties, you can set up equations based on the time between, until, or since a property value, or create custom equations based on an object's properties. 

For example, you can create:

• A company property that calculates the cost to acquire a new customer based on a custom equation.
• A contact property that calculates the average Amount of a contact's associated deals. 
• A deal property that calculates the amount of time since the deal was opened.

Depending on your HubSpot subscription, there is a limit to the number of calculation properties you can create.

Create rollup properties based on properties of associated records

You can create properties for an object that will be calculated based on certain property values of that object's associated records. You can calculate based on all associated records of that object or for specific association labels, including same object associations.

For example, you can create a deal property that counts the total number of sales activities logged for a deal's associated contacts with the Decision maker label.

• In your HubSpot account, click the settings settings icon in the top navigation bar.

• In the left sidebar menu, navigate to Properties.

• Click the Select an object dropdown menu, then select [Object] properties to create a property for that object.

• Enter the basic details for your property, then click the Field type tab.
• Select Rollup .
• Click the Rollup type dropdown menu, then select one of the following types:
  • Min: displays the minimum value for the selected number property on associated records of the selected object.
  • Max: displays the maximum value for the selected number property on all associated records of the selected object.
  • Count: counts the total number of associated records of the selected object that have a value for the number property selected.
  • Sum: sums up the values for the selected number property on all associated records of the selected object.
  • Average: calculates the average of the values for the selected number property on all associated records of the selected object.
• To choose how to format the property's values, click the Number format dropdown menu, then select from the following formats: Formatted Number, Unformatted number, Currency, Percentage, or Duration.
• If you select Currency as the format for a deal property, the property will use your account's Company currency by default. To use each deal record's currency, select the Use record currency instead of company currency checkbox. Using a record currency only affects how the value is displayed, and will not automatically apply an exchange rate.

• Select the associated object and associated record property to calculate the property based on. 
• To calculate based only on records with a specific label, click Select association labels, then select the labels you want to include.
• To add an additional condition, click Create condition. When an additional condition is set up, that condition must be met in order for the property to be calculated (e.g., calculate the property when Recent deal amount > 1000).
  • In the first dropdown menu, select a property.
  • In the second dropdown menu, select an operator that will compare the first property's value to a number or to another property's value.
  • In the third dropdown menu, select a property or enter a number that will be compared to the first property.

• Click Create. Once created, the property's displayed field type will be Rollup.

Please note: raw duration values are stored in milliseconds. If you're using a duration calculation property in other HubSpot tools (e.g., filters, calculation property formulas), you may need to use millisecond values.

Create time between calculation properties

You can create properties that calculate the time between two selected date picker properties within the same object. For example, you can calculate the time between when a ticket's owner was assigned and when the ticket was closed. Learn more about how Time Between calculation property values are displayed.

If you're trying to build a property based on today's date, learn more about how to use relative time in properties and filters.

• In your HubSpot account, click the settings settings icon in the top navigation bar.

• In the left sidebar menu, navigate to Properties.

• Click the Select an object dropdown menu, then select [Object] properties to create a property for that object.

• Enter the basic details for your property, then click the Field type tab.
• Select Calculation as the field type.
• Click the Calculated property type dropdown menu, then select Time between.
• Click the dropdown menus to select your Start date and End date properties.

• To add an additional condition, click Create condition. When an additional condition is set up, that condition must be met in order for the property to be calculated (e.g., calculate the property when Recent deal amount > 1000).
  • In the first dropdown menu, select a property.
  • In the second dropdown menu, select an operator that will compare the first property's value to a number or to another property's value.
  • In the third dropdown menu, select a property or enter a number that will be compared to the first property.
• Click Create. Once created, the property's displayed field type will be Calculation.

The following behavior is expected when displaying values for time between calculation properties:

• The time will be displayed differently in different HubSpot tools.
• For date properties without a timestamp, the default time used in the calculation will be 12:00 am UTC for the selected day.
• On a record (e.g., a contact record), the displayed value will be in seconds, minutes, hours, days, months, or years, and will be rounded to the closest whole number of the unit based on specific thresholds. For example, if the time calculated is one day and 12 hours, then the value displayed will be 36 hours, but if the time calculated is three days and two hours, then the value displayed will be 3 days. The thresholds for units are:
  • 12 years or longer will display in years and round to the nearest whole year.
  • 2.5 months or longer will display in months and round to the nearest whole month.
  • 2.5 days or longer will display in days and round to the nearest whole day.
  • 2.5 hours or longer will display in hours and round to the nearest whole hour.
  • 2.5 minutes or longer will display in minutes and round to the nearest whole minute. Otherwise, it will display in seconds.
• In custom reports that use a property with this field type, the displayed value will be in days. For example, if the time calculated is one year and twenty days, then the value displayed will be 385 days.

Please note: within HubSpot, the raw values of Time between and Average property types are stored in milliseconds. If an equation with these values yields a higher value than expected, use a millisecond converter to convert to your desired unit of time.

Create time since or time until calculation properties

To track the amount of time since or until a date, you can create a time since or time until calculation property. For example, you can create a Time since open property to track how long a deal has been open, or a Time until close property to track how far from today a customer is set to close.

A time since or time until calculation property's value is calculated based on the value of a date picker or date and time picker property. To create a property whose values display relative time based on today's date, learn how to create a relative time date/date and time picker property. Learn more about using today's date and relative time in HubSpot.

To create a time since or time until calculation property:

• In your HubSpot account, click the settings settings icon in the top navigation bar.

• In the left sidebar menu, navigate to Properties.

• Click the Select an object dropdown menu, then select [Object] properties to create a property for that object.

• Enter the basic details for your property, then click the Field type tab.
• Select Calculation as the field type.
• Click the Calculated property type dropdown menu, then select Time since or Time until.
  
  • For Time since, select the date/date and time picker property whose value will be the start date. The end date will always be today's date.
  • For Time until, select the date/date and time picker property whose value will be the end date. The start date will always be today's date.

• Click Create. Once saved, you cannot edit whether it's Time since or Time until, but you can select a different start or end date property.

Once you've created a Time since or Time until property, learn how to view the property and use it in filters.

Create custom equation calculation properties

You can also create properties that calculate values based on your own criteria. Custom equations can only be set up for number properties within the same object for which you're creating the new property. For example, you can create an equation to calculate the amount from a company's most recent deal (Most recent deal amount) compared to how many times your business contacted the company (Number of times contacted).

You can build your custom equation with arithmetic operators, comparison operators, logic operators, conditional statements, and other functions. These equations can return number, boolean, string, or date values. Learn more about available operators, functions, and how to format your formulas. You can also review examples of commonly used formulas.

To build a custom equation calculation property:

• In your HubSpot account, click the settings settings icon in the top navigation bar.

• In the left sidebar menu, navigate to Properties.

• Click the Select an object dropdown menu, then select [Object] properties to create a property for that object.

• Enter the basic details for your property, then click the Field type tab.
• Select Calculation as the field type.
• Click the Calculated property type dropdown menu, then select Custom equation.
• Click the Output type dropdown, then select the type of output your calculation will return. You can select one of the following: Number, Boolean, String, or Date.

Please note: the selected output type must match the output type shown in the top right of the editor or you'll be unable to create the property. For properties returning a date, you can use either the number or date output type which determines how values are displayed (i.e. number displays date in milliseconds, date displays formatted date).

• If you've selected the Number output type:
  • Click the Number format dropdown menu, then select how to format the property's values. You can select one of the following: Formatted Number, Unformatted number, Currency, Percentage, or Duration.
  • If you select Currency as the number format for a deal property, the property will use your account's Company currency by default. To use each deal record's currency, select the Use record currency instead of company currency checkbox.

Please note: using a record currency only affects how the value is displayed, and will not automatically apply an exchange rate. To add an exchange rate, you can include the exchange_rate or dated_exchange_rate functions in your equation.

• In the editor, insert, type, or paste functions and properties to build your custom equation. Learn more about custom calculation equation syntax. To insert formula elements:
  
  • Click the Insert dropdown menu.
  • To insert functions into your equation, select Functions, then select a function.
  • To insert properties into your equation, select Properties, then select a property. You can only create custom equations using properties of the same object for which you're creating the property.

• If your equation has errors, click [x] issues above the editor to view the reasons for the errors. You'll be unable to create the property if your equation has unresolved errors. Learn more about how to format your equations.
• To preview the output of your formula, click Test formula.

Please note: if any of the number properties in a custom equation have no value, or a null value, the calculation will not run and the calculation property value will be empty. The value will be empty even if there is a constant number included in the equation. In this situation, can use the if and is_known functions to set the value to 0 instead.

• Click Create. Once created, the property's displayed field type will be Calculation.

Custom equation syntax

• Literal syntax: using literal data, you can add strings of text, numbers, and true or false values to your equations. You can format strings, numbers, boolean, and date constants in the following ways:

  • String literal: text surrounded by quotation marks. These can be represented with either single quotes ('constant') or double quotes ("constant").

  • Number literal: numbers without quotation marks. Constant numbers can be any real numbers, and can include point notation. For example, both 1005 and 1.5589 are valid constant numbers.

  • Boolean literal: constant booleans can be true or false.

  • Date literal: a date in milliseconds. For example, 1698508800000.
• Property syntax: You can include other properties of the same object in your custom equation.
  • If a property is inserted into the editor, it's automatically formatted and validated as part of an equation.
  • If you choose to manually type or paste a property into the editor, it should be formatted as [properties.{propertyInternalName}]. For example, to add the property Test score, which has an internal value of Test_score, you'd type it into the editor as [properties.Test_score].
  • When using enumeration properties in an equation (e.g., dropdown select, radio select), values are considered strings so must be wrapped in double quotes ("e.g., "subscriber") and you must also use the internal name of the property options (e.g., "appointmentscheduled").

Operators

You can use operators in your equation to update, evaluate, or compare constants and property values. When using multiple operators, they follow the PEMDAS order of operations. Depending on the type of data you're using, you can include the following operators in your custom equations.

Functions

You can use functions within custom formulas to calculate values and complete actions with your data. Depending on the type of data you're using, you can include the following functions in your custom equations.

Examples

The following are example formulas based on common use cases.

• You can set up if else statements, including statements with multiple conditions and within functions:
  • if([properties.number] < 4, "less than four", "more than four")
  • if([properties.number] < 4, if([properties.other_number] < 2, "both true", "one true"), "not true")
  • concatenate(if([properties.number] < 4, "less", "more"), " than four")
• When using number properties in a custom formula, you can include the if and is_known functions to set the value to 0 when the number property is empty. For example, if(is_known([properties.number]), [properties.number], 0).
• When using a record currency in a formula, to add an exchange rate, you can include the exchange_rate or dated_exchange_rate functions in your equation. For example, [properties.amount] * exchange_rate('CAD').