Custom Equation: Amount / Time Between

[--Frank--]

Hi,

Part of our business is selling services. I'd like to create a new Deal Property that gets its value from a calculation.

In two deal properties we set 'Start Date' and 'End Date'. This allows for a quick (and correct) return for 'Project Duration'.

Now I want to use the output of 'Project Duration'  and the deal 'Amount' to calculate the value of a new Deal Property --> Monthly Contract Value. 

In my simple attempt, I created this calculation, Amount divided by Project Duration (see below). However, it returns the number Zero.

Tried to multiply the outcome by Milliseconds per Day, but still no luck.

Is this possible at all? Also playing with other functions, I seem to get a lot of errors when trying to save the function.

• ([properties.amount] / time_between([properties.estimated_project_start_date], [properties.estimated_project_end_date__duration_]))

[nickfromfargo · Accepted solution]

TLDR;The main issue seems to stem from the original equation. I think you are using the duration property in your equation as a time between factor... instead of the end date.

Not totally sure on how I'd do it for a Monthly breakdown, but.

You can do a Revenue per Day calucation all in one calculated property if you cofigure it as below swapping out your appropriate properties. Be sure the Output type is set to Number and formatted to Currency. 

([properties.amount] / (time_between([properties.estimated_project_start_date], [properties.estimated_project_end_date]) / 86400000))

[ptitean]

The division by 86400000 is what did it for me! I learned that time is stored in milliseconds in HubSpot, and that is how many milliseconds are in days. So if your numbers are looking super high, that's because it's showing you the # of ms 🙂 thanks for the help!

[KBravender]

@nickfromfargo this calculation is great!  I set it up and it's 100% accurate.  My issue is how to use this in a report that shows Deal amounts broken out by month.  Any additional insight is truly appreciated. 

[Jaycee_Lewis]

Thanks for sharing this, @nickfromfargo 🙌 — Jaycee

[Jaycee_Lewis]

Hey, @--Frank-- 👋 Did you make any progress?

If not, I have a few questions to help our community better understand the issue.

Questions:

• What data type is properties.amount and what data type is the result of time_between?
• If time_between returns a value in days, are you expecting Monthly Contract Value to represent the average amount for each day of the project or for each month?
• Can you share the errors you receive when trying to save the function, please?

Best,

Jaycee