Date between stages (stages captured elsewhere and single value relates latest)

Ask for Help
1

Hi gliders, need some help please

I have a CRM, each record has a ‘status’. I track the date each time the status is changed, therefore have a ‘status change’ database and relate back the latest status change. That’s fine.

But I want to measure the date between statuses. Not the average either, but how long has this record taken between Stage 1 and 2, or 2 and 3, etc.

I need this to be non-dynamic (i.e. to save how long it took between stages).

Hopefully I’m missing the obvious… Thanks!

2

The Date, or the duration?

Seems that you already have everything that you need to calculate this. Where are you stuck?

3

Hey Darren, the updates are captured in a new table, meaning a new row for each update. I can’t work out how to get the data into one row to calculate. I have 7 different stages, so not too many to make a column for each stage, but I can’t work out that either.

4

*yes, duration between dates / stages

5

Can you show me ideally how you would want it to look in the data editor table?

6

image
image1444×224 17.5 KB

I want to move the date of each stage currently in rows into columns, so new headers would be:

Date of Screening | Date of Snapshot | etc.

In Excel this would be achieved via ‘unpivot’ but I don’t know how to do here

7

Okay, so you just want a single row that records each of the dates in separate columns, right?

From a high level, what you would do is:

  • Create the row when the parent record is added, and set the first timestamp.
  • Include a value that allows you to link back to the parent record (ie. parent RowID)
  • Create a Single relation from the parent to the “history” row, then each time a stage is updated, set the appropriate duration column value via that relation
8

Thanks Darren, let me clarify.

Parent sheet: includes company name (unique id)
Updates sheet: every time update is changed a new row added here (inc. stage and date).

I want to record how long it took from stage 1 to 2; 2 to 3; etc. For this I think I need a set of columns, one for each stage date. Then these are set statically and I can measure between dates.

When I relate, I get multiple hits and I can’t work out how to pull a specific hit, i.e date from a particular stage.

Does that help?

9

Okay, I think I understand now.

What you can do is create a Query from each Stage to the previous stage, then use a Single Value column to fetch the Date of the previous Stage, then use that to calculate the duration by subtracting from the Date of the current stage.

So…

In the Updates table:

  • Query column that targets the same sheet, with the following filters:
    – CompanyID is This row->CompanyID
    – Stage number is less than This row → Stage number
    – ORDER BY Stage number descending
  • Single Value column to fetch the first Date via the Query column
  • Math column to calculate the duration:
    – Round(Date - PreviousDate)

Does that get you what you need?

10

Thanks, Darren. Let me have a go at this and come back here. As the stages are names not numbers, would you recommend making an index so I can use the “<” logic?

11

Yes, that would be a good idea.

12

Darren - thank you!! I had no idea how to do this and think I need to learn more about query - so powerful. Thanks so much.

1 Like
13

Yes, it is. Very powerful.

Just thinking about this one a bit more, if you can be guaranteed that stages will always be completed in chronological order, then you could get away without the stage numbering, and instead filter by date.

So the query filters would be:

  • Company ID is This row->Company ID
  • Date is before This row->Date

And then instead of a Single Value column, you could use Rollup->Latest date

14

Understood, thanks for the additional thought. In this case they should go in order but the interface allows going back or skipping. I did think about changing that but I’ll run it for a while and see first. Actually as I wrote this I realised that allowing the user to downgrade the status would screw things up… will think more on this!

15

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.