HubSpot Ideas

basil

Allow Multiple Conditions for Calculated Properties of Type Count / Sum

It is great that we can count or sum over objects using calculated properties. However, we often have cases where we want to put not just one but multiple conditions. So if we could have filter functionality similar to the normal list filters or workflow filters where we can combine multiple conditions with AND / OR that would make calculated fields much more powerful for us.

HubSpot Updates
April 20, 2023 11:03 AM

Hi folks,

 

I'm happy to report that this Idea is now In Development! Our team is actively implementing a solution for this feature and we are super close to an alpha.

We are currently looking for a select few willing to give feedback to be part of the alpha. 

If you are interested in being a participant, please fill out this form. Once we're alpha-ready, we'll contact you with the next steps. 

 

Best,

Shay

July 15, 2022 10:33 AM

This is all great feedback. These are the two primary points I'm gathering: 

  1. Today only properties, not reports can trigger workflow automations. Automation compatibility and general flexibility is really important. 
  2. Pricing and packaging might be a barrier with datasets being Ops Hub Enterprise

Two questions:

  1. Is there ever a need to compare these calculated property values in a summarized report visual? Or are these properties realistically just operational and not being used for analytics? 
  2. Can yall chime in with even more specifics/examples around how you're using these properties in workflows/how these properties are being used broadly?

Being Reviewed
July 13, 2022 01:17 PM

Howdy, 

I'm a HubSpot reporting PM and noticed this was a highly requested feature set for calculated properties. It seems like the goal of this thread is to apply one or more conditions to a a set of data and then create either a sum or a count based on the conditional filters. This can be achieved through datasets + reporting today -- pictures attached. I'm curious if the pictured reporting solution solves the user problem in this thread or is there a next step that requires this information to be saved in a property instead of a report? If yes, I would love to hear what that next operational step is and how you're using this aggregated data next, because our team is actively thinking thinking about this connection point.



Conditional Logic using if() function in datasetsConditional Logic using if() function in datasetsReporting enabling the ability to create a summation or a countReporting enabling the ability to create a summation or a count

40 Replies
basil
Key Advisor

Also we often need conditions choosing multiple labels and currently we can only select one single label.

 

screenshot.png

MatP
Participant

Hi, 

 

I second this idea, this would also be a useful addition for our internal use cases.

Thank you, 

 

MatP

ParkerShort
Contributor | Diamond Partner

We're looking to use this to calculate revenue of certain types of deals, either based off of product type or pipeline. For instance, if we want to track all closed won deals in a specific pipeline, we need two criteria, but that's currently not possible (there are some workarounds, but they're ugly).

yasiooy
Participant | Diamond Partner

Me and my team would also love to have that option, we have multiple clients that could benefit from it.

SarahMayo
Contributor

Please implement this! 🙂

Hawk-Steve
Top Contributor

+1 from me, would be a great improvement on what currently exists.

 

A current use case:

  • I need a tally of how many associated deals a contact has within 2 stages on a sales pipeline.
LindsayMB
Member | Elite Partner

We also need something like =SUM((%-%)*whole number). Is there a workaround and I'm just not seeing it?

joseph_cone
Participant

please Hubspot, calculated fields would be SUPER useful if we could add more criteria. 

- Use case: need to sum associated deals closed in a timeframe/year, but ONLY if the association (label) is X

SKripp
Participant

Need this!!!!!

CColegrove
Contributor

Found myself here after trying to do this myself. We have multiple Resource Types with their own associated costs for our services. Trying to do: 

CColegrove_0-1641297469213.png

That will then feed into an above cost of whatever that role equals. 

GrantCarlile
Top Contributor | Diamond Partner

Agreed, this would be great.

PVignon
Member

We need this 

 

Viddy
HubSpot Employee

hi team, posting on behalf of my customer, they are also looking to be able to add more than one condition for each calculation property because at present they are creating separate calculation properties for each condition and it is not a sustainable long term approach for them. 

Bob2245
Top Contributor | Platinum Partner

While adding multiple conditions is handy, there's a fairly easy workaround for this that will solve most cases. In this example, I want to calculate the revenue over 2021 in a Company calculated property, so I need to sum the amount of associated deals, where close date is inside 2021 and stage is closed won. This goes as follows:

 

  1. Create a Single line text property. Name it whatever you want. This property will only act as a condition property and won't be visible for regular users. I like to label properties like this with the affix "SYSTEM - " to scare off any non-tech users. In my example I used "SYSTEM - Revenue year"

  2. Create a workflow with a trigger for each of the criteria you want to use. For instance:
    1. Deal stage = Closed Won
      AND
    2. Close date is between 01-01-2021 and 31-12-2021

  3. Use an action step to set the filter property from step 1 to a value that you will use in your condition. In this case, I use "2021"

  4. Now create your calculated property, in this case a sum of associated deal amounts, and set the condition to be  "your filter property == value from step 3". In my example "SYSTEM - Revenue Year == 2021"

  5. And presto! Your deal amounts will be summed nicely (or whatever your use case is), but only if they meet your multiple conditions 🙂
BrunAlbuquerque
Participant

Adding my request as well.

 

I created a calculation property, but actually couldn't achieve what I was looking for. So I'd like to receive a help please!

 

- I'm setting targets for the sales team - they have kind of a multiplier for their targets depending on the lenght of the contract and ARR. 

So it's like:

1 year contract = ARR

2 year contract = 1,22 *ARR

3 year contract = 1,33*ARR

 

Is it possible to create a calculated property with 3 different conditions? I kind new at Hubspot, used to work with SFDC, so struggling a bit. 

 

Thanks!

basil
Key Advisor

We would also need this

smcmillian
HubSpot Product Team

Howdy, 

I'm a HubSpot reporting PM and noticed this was a highly requested feature set for calculated properties. It seems like the goal of this thread is to apply one or more conditions to a a set of data and then create either a sum or a count based on the conditional filters. This can be achieved through datasets + reporting today -- pictures attached. I'm curious if the pictured reporting solution solves the user problem in this thread or is there a next step that requires this information to be saved in a property instead of a report? If yes, I would love to hear what that next operational step is and how you're using this aggregated data next, because our team is actively thinking thinking about this connection point.



Conditional Logic using if() function in datasetsConditional Logic using if() function in datasetsReporting enabling the ability to create a summation or a countReporting enabling the ability to create a summation or a count

ParkerShort
Contributor | Diamond Partner

@smcmillian Thanks for chiming in. I'd love to see the formula feature from datasets replace the calculated properties feature, if that's what you're proposing. It's much more powerful and opens up a ton more options. As long as it's available in all the same places as a calculated property, that would definitely work.

abrow
Contributor

@smcmillian I wouldn't think reporting would allow for workflow automation. We want robust calculated fields so that we can build workflows off of them. We also connect HubSpot data to other systems and the reporting feature wouldn't help in that case either.

Hawk-Steve
Top Contributor

Good thoughts on this @smcmillian.

 

I have quite a lot of automation that run off of calculated properties. Such as triggering alerts when a contact record is associated with more than 5 open tickets in a specific pipeline. The calculated property would need to have 2 conditions - Pipeline AND open status.

 

To do this in today's portal I instead have to create additional properties, workflows, etc to create a single property that can be used in a condition.

 

 

The other thing to note is that calculated properties are available to certain Hubs and grades, datasets require the purchase of opshub enterprise. It's a bit of a hard sell when a complicated workaround comes at no additional cost (If we're not talking sanity or system health 😉)