Track time spent in status

Get Help
1

Hi there!

I have one task that I don’t understand how to solve. I have already read this topic and looked this example. However, there is still no suitable solution.

I have 2 database: State and Lead. My goal is track how much time Lead spend at each State. Ideally, I want to get something like the graph in the image.

img2
img21207×790 38.8 KB

One of the solutions is to create an additional field for the lead and write down the date of transition to the next status in it. And also create a field with a formula that will calculate the number of days that the lead spent in the status (Days in status).

But there’s a problem. If I have 5 statuses, then the lead will have 10 fields. This will greatly interfere with readability.

Maybe there is some more elegant solution?

1 Like
2

I think it should be possible using reports, but I have a question: what does the month mean in the image you shared? Is it the month that each Lead was created, or is it something else?

Say I have two Leads that can be in states A, B, C, D and the following transitions, what should the chart look like?

Lead 1
20th Jan - created (state A)
5th Feb - changed to state B
10th March - changed to state C
11th March - changed to state A
4th April - changed to state D

Lead 2
25th Feb - created (state A)
3rd March - changed to state B
7th March - changed to state A
10th March - changed to state C
20th March - changed to state B
5th April - changed to state D

(assume that today is 10th April)

The reason I ask, is to determine what the y-axis value(s) for state A or state B should be, for example.

3

  1. “What does the month mean in the image you shared?” – Yes, it does. Those leads that were created in May are included in the selection for this month.

  2. “…is to determine what the y-axis value(s) for state A or state B should be, for example.” – I think it should be the difference between today and the date the lead was created, expressed in days.

For example, for Lead 1 there will be the following status statistics:
A: 50 days (11th March - 20th Jan)
B: 33 days (10th March - 5th Feb)

But if we can set a condition, for example, for status B “If we move the Lead from status A, then we calculate the transition date. If not, we don’t calculate it.” Not sure if this is even possible :sweat_smile:

In this case, Lead 1 will have the following values:
A: 16 days (5th Feb - 20th Jan)
B: 33 days (10th March - 5th Feb)

This is not entirely correct and there are many nuances, but I do not find a better solution.

4

So here is how I think you should do it:

  • create a chart report using historical data for Leads
  • use State as the x-axis
  • use the formula below for y-axis
  • choose a line chart
  • use Creation date for colour coding (grouped by month)
SUM(
	DATEDIFF(
		[Modification Date],
		IF(
			DATEPART(
				[Modification Valid To],
				'year'
			) == 9999,
			NOW(),
			[Modification Valid To]
		),
		'day'
	)
)

Here is a gif of me doing it (but the resulting chart looks flat because my database is less than a day old!)
firefox_IdJ1C2f9ev

Note: you will probably want to give your y-axis a more meaningful title(!)

firefox_fLchLZRmzT

and the report can get a better name than New report :slight_smile:

Let us know how you get on :slight_smile:

5

It’s done. Thank you so much for your help!

We also thought about how to exclude extreme values. For example, when a lead card goes through the entire funnel in a day. This happens if a client worked with us on another project and immediately wants to move on to another stage.

But we didn’t come up with any optimal solution :slight_smile: