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.
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).
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.
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:
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"
Create a workflow with a trigger for each of the criteria you want to use. For instance:
Deal stage = Closed Won AND
Close date is between 01-01-2021 and 31-12-2021
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"
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"
And presto! Your deal amounts will be summed nicely (or whatever your use case is), but only if they meet your multiple conditions 🙂
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 datasetsReporting enabling the ability to create a summation or a count
@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.
@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.
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 😉)