Calculation Property Doesn't Work if a Property is Blank

[GiantVoices]

Hello, 

Currently, we have the enterprise level HubSpot account and have been working on creating calculation properties to add together transactional pieces. There is a major roadblock with this property as it doesn't allow a calculation formula to run if there is a blank property. Is there any sort of workaround for this problem? 

Thanks, 

KP

[MelindaNelson · Accepted solution]

Hi all,

I tested this with a customer today. Setting the properties included in the calculation property to 0 did work.

To configure this, you can:

• Create the calculated property using two number properties.

• On the contact record, add 0 for any values that are unknown.

When the value for Number property 2 is blank, the Calculated number property is unknown.

When the value for Number property 2 is 0, the Calculated number property is calculated.

I hope this helps! Please let me know if you have any questions! 

Thanks,

Melinda | (She/Her)

^{Customer Support Specialist}

[Clare_HS]

I couldn't use `is_present` for some reason. I wound up setting the unknown values to 0 within the formaul like:

if(is_known([properties.abc]),[properties.abc], 0) + if(is_known([properties.xyz]),[properties.xyz], 0)

So we're saying that if the NUMBER property has a value, use that vlaue in the calculation. If there's no value, call it 0 and calculate accordingly. 

That worked like a charm for me after a little trial and error 😀

[markmhaddad]

Yup, HubSpot changed the is_present function to is_known (presumably to align with the terminology they use in filters)!

[hub_muc]

Alternative solution without using a workflow: Use Advanced Mode in the Calculation Property and combine if and is_present function before adding the properties:

(if(is_present([properties.xy]), [properties.xy], 0) +

[JoshBarber]

This works a treat for calculations that require you to calculate with other calculations/rollups where you can't set the default value to 0, and should be marked as the solution. No longer need workflows.

[markmhaddad]

Under-rated comment here - this is the best solution!

[FZangerl]

At first: big shame on Hubspot that the paying users have to find workarounds for problems like this - problems, that appeared already years ago. 

At second: in my opinion the easiest solution is to use a workflow and the funktion

"Increase or decrease property" and increase the value by 0, because unknown + 0 = 0

Best, 

Florian

[MarijnAIHR]

Hi @MHill4 
Thanks for this workaround. Could you maybe share a screenshot of your solution? I cannot get it to work myself - if I multiply an unknown value times 1, I still get unknown, not 0...

---

[JoshBarber · Accepted solution]

In case anyone stumbles upon this like I did - I also couldn't get this to work and instead used workflows to set the default property to 0.

[MHill4]

Sorry, by Constant 1, I just meant the number 1. 

In the calculation property A for example, your formula would be: (Property A x 1)

That way unknowns for Property A stay unknown, but you can still include those in calculations as 0.

So for @GiantVoices example above. You would make a new calculation property for each of the properties in the proposed formula. For example "2015 Q1 Payment Amount Calc," which would equal (2015 Q1 Payment Amount x 1).

For records with unknown 2015 Q1 Payment Amount, 2015 Q1 Payment Amount Calc = 0

For records with a number entered in 2015 Q1 Payment Amount, 2015 Q1Payment Amount = the same number.

does that help?

[MHill4]

Hi All,

I believe I may have found a solution to this issue. A bit of effort upfront, but it definitely works!

Lets say: Property A + Property B + Property C = Calculation Property X

For each of Property A, B and C, just create a new calculation property, maybe labeled "Property A Calc", where you do (Property A x Constant 1) = Property A Calc. If Property A is blank, the Property A Calc property will be 0. If Property A is known, Property A Calc = Property A.

So then to get Calculation Property X, you would just do Property A Calc + Property B Calc + Property C Calc. Then it runs perfectly! AND you don't need to do any workflows and you maintain the blank status of the original field which is important!

[andresmorera]

Thank you MHill4,

What should be the value for Constant 1 in the formula or should I create a new porperty named Constant 1?
Thanks for clarifying.

Andres

[sgerrits]

I've built workflows that set the property to 0 if unkown but then the problem is if you have a calculation property that references another calculation, there's no way of getting the second calculation to work if all of the criteria are not met.  Pretty frustrating.  

[CTeeling]

@MFrankJohnson 

Hi, Frank, 

I'm running into this exact issue now - Is there a best practice to set multiple default values to zero in a single workflow, or did you have to create multiple workflows to set multiple default values?

[Deflow]

The simplest way I have found to do this is as follows:

• First we want to check the value to see if there is anything entered in there. Hence "less than or equal to $0 or EMPTY".
• If value is empty, then set the property to be 0.
• If value is not empty or is greater than $0 then "go to action"

• This second action is repeating the same steps as above on the next value that needs to be checked.

I do like the method above using calculated values but if you are not on an enterprise account then you need to use your 5 calulated fields sparingly.

[MelindaNelson · Accepted solution]

Hi all,

I tested this with a customer today. Setting the properties included in the calculation property to 0 did work.

To configure this, you can:

• Create the calculated property using two number properties.

• On the contact record, add 0 for any values that are unknown.

When the value for Number property 2 is blank, the Calculated number property is unknown.

When the value for Number property 2 is 0, the Calculated number property is calculated.

I hope this helps! Please let me know if you have any questions! 

Thanks,

Melinda | (She/Her)

^{Customer Support Specialist}

[CleberAlexandre]

Where can I configure this?

• On the contact record, add 0 for any values that are unknown.

[Ryamak]

I'm not sure how this solution would help if the calculation property is calculating "Time Between". How do you put in a 0 value for a date? And even if there's a way, what about in an example where you're trying to calculate how long a deal has been in each stage for? If the deal is still in that stage you don't want a date set for it yet, but it's still a running count which isn't even being counted.

[MFrankJohnson]

Just want to add a workflow to make sure that all blank properties used in calcuated property formulas are auto-updated to zero as new contacts/objects are added to the CRM. 🙂

[huentt]

I also encountered the same case
I think Hubspot should add one condition when setting up fields with the following numeric format: if the field has no value, there are two options for users to choose to be blank or zero.
so the calculated fields will work

[SteveES]

Any updates on this? I just ran into the same challenge and I'm hoping someone has a work around.  Thanks.