Would like to be able to roll up minimum or maximum dates from child to parent.
Would also like to flag the record as being included in the calculated field so they can be enrolled in workflows.
For example:
Which contact has the Minimum (first) Became an MQL date? Copy that first MQL contact's source type to the company.
EDIT: Workaround similar to what @kaious posted but with timestamp offsets.
Would still like this functionality, but wanted to share a (cumbersome) workaround we are using with fellow users. We have Marketing and Sales Enterprise.
Step 1: Create a helper calculation property on the child object. Insert your date property and format it as a number. The number returned is in UNIX.
UNIX timestamps represent the number of seconds (or milliseconds, in this case) that have elapsed since the Unix Epoch, which is January 1, 1970, at 00:00:00 UTC. It is independent of any specific timezone.
Helper Recent Sales Email Open Date
Step 2: Create a helper rollup property on the parent object. This will be set to max or min. Select your helper property from the child object. The number returned is still in UNIX.
Parent Object Helper Property
Step 3: Create a calculated property on the parent object. Subtract the appropriate amount of milliseconds from your rollup property (for your timezone) and format as a date.
This is not a perfect solution. I am in a region of the United States that uses daylight savings time. Therefore, time away from UNIX can vary throught out the year. Also, I had to create 3 calculated fields to get this estimated answer.
Ultimately, I am just trying to get the right day even if the exact time is off. This workaround will handle situations where the timestamp is closer to midnight.
BONUS:
I have used a larger offset when calculating quarters from dates. This is because reps often update records into the night to make EOQ deadlines. I don't need the exact datetime, to get the right YYYY-Q#. We create custom properties like this since we do a lot of analyses in spreadsheets. Exporting our date groupings reduces effort.
I absolutely agree this is needed. Having this functionality would make HubSpot's data automation much more powerful, especially when rolling up data from the contact to the company. It would be very impactful for a lot of HubSpot users to be able to see the milestone contact lifecycle stage dates rolled up to the associated company. Then you could have a high-level view of how that company as a whole has moved through your lead process.
I didn't realize this wasn't possible until now. I need a way to pull the min date from an associated deal on the contact. I've also had to build out a workflow for the exact use case that mslarson pointed out above. It would have been helpful to be able to gauge the lifecycle stage milestones of the contacts on the company level.
Would be amazing to have the date of the first and last deal on the contact, using a custom date property on the deal, without any coding. Our deals are generated externally and then pushed into HS over API. Currently we have a workflow with custom code to get the date of the last Deal, comparing all Deals on the custom date property 'Date Submitted'. I would like to do the same with other custom date properties on the Deal and additionally have the date of the first deal by these custom date properties on the contact. But each time I want to have an extra date, I need a developer to adjust the custom coding for me. Since min and max are there, it would be great to have it return dates as well. Thnx!
I assumed this was basic functionality that was already available until I went to create said property and realized it's not. This would be very useful in reducing the amount of workflows necessary and avoiding issues.
For anyone trying to solve for this that has a few calculation fields spare in their allowance, there is a not-too-annoying work around:
1) create a calculation field of type unformatted number on the object that has the date field you want to calculate from, then make this field equal the date field - that will result in the field being the UNIX ms value of the date field
2) create a calculation field on the associated object where you want to display the min (or whatever date). Make this field a min (or whatever) calculation based on the field created in step 1 – this will mean the field displays the lowest/highest/whatever UNIX timestamp 3) create another field on the associated object that is a date format, make this field equal to the field in step 2 - this will mean the UNIX timestamp is converted to a HubSpot date field.
I would very much like to see this and am surprised it has less votes than I would Expect. Our use cases result from the fact that we have user activity from our proprietary application automatically updating via API. I have many dates tracked regarding the last date that user X did thing Y. If the max date of all the contacts using a certain feature is long ago, thats in indicator that an action should be taken or possibly that our customer (the company) is in jeopardy. Also I simply want to know how long a customer has been a customer related to a min date on either contacts or custom objects where we store instances of customer tenants. Thank you!
This would be extremely helpful. We can use numbers, so please let us grab the minimum/maximum dates on the associated records. This could greatly help with the most recent or first dates calculations or reports.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.