Help with Calculated Property for YoY Comparisons

[Matt-B]

Hey everyone,

I'm trying to set up a calculated property in HubSpot and running into some trouble.

I have a calculated property called "Time Since Deal Close", which uses the "time between" calculation type to output values like 30 days, 180 days, etc.

Now, I want to create another calculated property for year-over-year (YoY) comparisons that follows this logic:

• If Time Since Deal Close ≥ 365 days, set the value to 1
• If Time Since Deal Close ≥ 730 days, set the value to 2
• And so on...

The issue I'm facing is that the time property doesn't seem to function like a number, so I'm struggling to set up these conditions properly.

Has anyone successfully implemented something similar, or is there a workaround? Any advice would be greatly appreciated!

Thanks!

[karstenkoehler · Accepted solution]

@Matt-B just ran a test by creating a time-since property and exporting the values, and it seems like HubSpot is for once not storing this information as milliseconds but actually hours, minutes, seconds.

Checking again, I'm seeing that you're apparently using a custom today's date property (and not a time-since property)? If so, run a quick export for a couple of deals to see which values HubSpot actually stores in your time between property – once you have that information about the exact value, you can adjust your calculation accordingly.

[ACarr1 · Accepted solution]

Hey, I set something up similar recently. Here is how its setup:

Create two properties: LTM Deal and Prior LTM Deal

Populate "Yes" for each via workflow (close date is less than 365 days for ltm and mor than 365 AND less 730 for prior) 

Create two rollup properties for LTM and Prior Revenue where additional condition refers to the properties created in the first step

Create calculated property that calculate LTM Revenue - Prior LTM Revenue (you'll need to set it up like this to avoid blank values: if(is_known(LTM), LTM, 0) - if(is_known(Prior LTM), Prior LTM, 0)

Hope that's helpful

[karstenkoehler]

Hi @Matt-B,

Can you please share a screenshot of your formula and of the exact error message?

Best regards

[Matt-B]

I tried changing the formula hoping it would maybe be possible calculated with milliseconds but it didn't seem to help.

I am wanting to flag deals so that I can use rollup figures with that number as the filter to compare YoY figures for deals associated to a company (that can be used in a scoring property).

[karstenkoehler · Accepted solution]

@Matt-B just ran a test by creating a time-since property and exporting the values, and it seems like HubSpot is for once not storing this information as milliseconds but actually hours, minutes, seconds.

Checking again, I'm seeing that you're apparently using a custom today's date property (and not a time-since property)? If so, run a quick export for a couple of deals to see which values HubSpot actually stores in your time between property – once you have that information about the exact value, you can adjust your calculation accordingly.