How to sum values from all associated deals on company level?

[joel_vandusen]

Hello!

I am working on gathering intelligence from associated deals on the company level. What I am trying to do is get an accurate number of total number of paid licenses for a company, when they've purchased users across multiple deals. What I am wondering is if there is a way to add a date based condition when using the sum calculated property? I would like to create some sort of logic that says sum all active paid licenses in closed won deals IF subscription start date is today or in the past AND subscription end date is today or in the future. 

Thank you for any help you might be able to give!

[karstenkoehler · Accepted solution]

Hi @joel_vandusen,

Calculation properties support conditions but only simple conditions, not 'complex' conditions like your dates.

The workaround would be making your condition information available in a single property, e.g. a checkbox property with the wieldy name 'subscription start date is today or in the past AND subscription end date is today or in the future'. This single checkbox property could be referenced as the condition in the calculated property. It would be set by a deal-based workflow.

This deal-based workflow would ideally be a scheduled workflow that runs daily (Operations Hub Professional required), stores the daily date (the date of the step) in a custom date property called Today's date and then checks whether the subscriptions dates behave as you described. If yes, the single checkbox would be set to checked. If not, it would be set to empty.

Without scheduled workflows, you would need a workaround like the one described here for today's date property.

Let me know if I should elaborate any step more in detail.

Also, if you're not using Operations Hub Professional yet but this use case has you convinced, let me know. As a HubSpot solutions partner I can check discounts for you.

Best regards!

[joel_vandusen]

Thank you! This is so helpful. I went in and created the today's date
property using the linked workaround. I think the only thing I'm still
struggling with is what my enrollment trigger should be on the deal to set
the subscription start date is today or in the past AND subscription end
date is today or in the future field correctly and be re-enrolled?

[karstenkoehler]

Hi @joel_vandusen,

The deal-based workflow could re-enroll based on "Today's date is known", that means it would everyday, effectively. ('is known' means the same as 'has changed' here.) Is that what you're referring to?

Best regards!