I’m looking to create reports that are based on the month.
For example, the date is currently January 1, I’d love to track sales leads enrolling four to five months into the future. This would mean, the report would only show leads from May to June.
Ideally, this would also be relative monthly. The leads shown are supposed to be static until the month ends or if a property changes. So if it currently is January 1, it should show all leads from the entirety of May 1 to June 30. At January 31, it should still show all the leads from May 1 to June 30.
Accordingly, at February 1, it changes to June 1 to July 31 instead, and so-on.
Our HubSpot plan only allows relative tracking either daily, this/last/next month, this/last year, next/last quarter, yesterday/tomorrow.
I was thinking of using calculated properties to achieve this by assigning a number to every month, then calculating this number per company (for example, 1 for January, 12 for December), then using another calculated property/filters to only show X months into the future.
Any way to achieve this? Would it even be possible? Would love to know if there is an easier way to do this.
yeah calculated properties can get you part of the way there but its a bit roundabout. the main issue is hubspot doesn’t give you a native “current month” function in calculated properties.
what i’d do instead: set up a simple workflow that runs on the 1st of each month and updates a single-line text property like “report_window_start” and “report_window_end” with the dates 4-5 months out. then use those properties as your report date filters. way more reliable than trying to do date math in calculated properties.
if you want zero maintenance, another option is using the custom report builder with a relative date filter set to “is after” a rolling date, then adding a second filter for “is before” another rolling date. on professional you should have access to that. not as precise as exact month boundaries but gets you close.
You can leverage the ‘lead/deal create date,’ create ‘expected close date’ calculated property that is create date + 150 days and then for reporting you can look at expected close date by month and you’ll have what you’re looking for I believe!
I had some fun with this one haha. I’d use a calculation property like this. It outputs the month & year. This would be more for the display. For the actual filter, you can probably just create a calculated property which adds/subtracts 5 months * miliseconds/month to the date, and then check when that “fake” date property is this month / next month.
You can copy-paste this:
if(month([properties.closedate])=1, concatenate("May ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=2, concatenate("Jun ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=3, concatenate("Jul ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=4, concatenate("Aug ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=5, concatenate("Sep ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=6, concatenate("Oct ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=7, concatenate("Nov ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=8, concatenate("Dec ", number_to_string(year([properties.closedate]))),
if(month([properties.closedate])=9, concatenate("Jan ", number_to_string(year([properties.closedate])+1)),
if(month([properties.closedate])=10, concatenate("Feb ", number_to_string(year([properties.closedate])+1)),
if(month([properties.closedate])=11, concatenate("Mar ", number_to_string(year([properties.closedate])+1)),
if(month([properties.closedate])=12, concatenate("Apr ", number_to_string(year([properties.closedate])+1)),
"error"))))))))))))
