topic Re: Average time between two fields in Dashboards & Reporting

Average time between two fields

SGarneau — Tue, 10 Oct 2023 13:41:43 GMT

Hi !

I want to create a report that will tell me what's the average time between a deal is closed won and activates.

For now, I'm tracking the dates through 2 custom fields I created (date-picker). One is Closed Won Date and the other is Activation Date. But I can't seem to find a way to associate them and have an average come out of this.

There might be other ways to track this data ?

I'm open to suggestions on how to create that type of report !

Thank you in advance,

Re: Average time between two fields

karstenkoehler — Tue, 10 Oct 2023 13:44:09 GMT

Hi @SGarneau,

This is best done through a calculation property (option: time between), see here: https://knowledge.hubspot.com/crm-setup/create-calculation-properties

Once this property is created, you can display it in reports, for example with the KPI chart type.

Best regards!

Re: Average time between two fields

SGarneau — Tue, 10 Oct 2023 14:22:57 GMT

Hi @karstenkoehler ,

Thank you for your fast reply. I just was able to create a field Average Activation with the calculation property, thanks ! However the data populated is a date that doesn't make sense. How can I visualize a number of days ?

Thanks !

Re: Average time between two fields

karstenkoehler — Tue, 10 Oct 2023 14:23:42 GMT

Hi @SGarneau,

Can you elaborate what doesn't make sense? I'm not sure I'm following. The more details you provide, the better I can help.

Re: Average time between two fields

SGarneau — Tue, 10 Oct 2023 14:30:41 GMT

Hi @karstenkoehler

Let's say Field 1 is Closed Won Date [date picker] and Field 2 is Activation Date [date picker]

Now I have my [time in between] field 3 that calculates time between Field 1 and Field 2, however the data showing up is a date, but I'd need it to be a round number.

(i.e: Field 1 = 10/10/2023 and Field 2 = 11/10/2023, I'm expecting Field 3 to = 1 day instead of 01/01/1970 - which does not make sense)

Thanks !

Re: Average time between two fields

karstenkoehler — Tue, 10 Oct 2023 14:36:56 GMT

Hi @SGarneau,

In your calculation property settings, did you choose the "Time between" option from the drop-down?

Re: Average time between two fields

SGarneau — Tue, 10 Oct 2023 14:42:49 GMT

@karstenkoehler yes !

Re: Average time between two fields

karstenkoehler — Tue, 10 Oct 2023 15:06:24 GMT

Hi @SGarneau,

It looks like you set everything up correctly. This might be a bug, it's hard to say without access to your portal I'd recommend checking with HubSpot support.

Re: Average time between two fields

deepikaverma — Wed, 11 Oct 2023 07:14:56 GMT

Hi @SGarneau ,

This would be a temporary bug since I tried it and it is giving me the values in numbers only not in dates.

Thanks!

Re: Average time between two fields

elizheleva — Wed, 11 Oct 2023 14:24:57 GMT

Hi, @SGarneau ,

From the surface, it does look like everything has been set up correctly. I would be interested, though, in finding out how the custom properties are being updated. Is it possible that the formatting of those properties are causing a glitch in the calculation?

It will be beneficial if you could share a screenshot from a specific deal (of course, removing any sensitive information) showing the values for the custom properties, based on which you have the calculation. Also, further context on how those are updated can be valuable, too. Is it via a workflow or in another way?

Thanks,

Eli