# Date between stages (stages captured elsewhere and single value relates latest) **URL:** \ **Category:** Ask for Help\ **Created:** [April 1, 2024, 2:53am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203 "2024-04-01T02:53:13Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 2:53am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/1 "2024-04-01T02:53:13Z")
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! ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 1, 2024, 3:10am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/2 "2024-04-01T03:10:43Z")
> [@Matthew\_Viner](#): > > 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. The Date, or the duration? Seems that you already have everything that you need to calculate this. Where are you stuck? ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 3:15am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/3 "2024-04-01T03:15:02Z")
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. ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 3:15am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/4 "2024-04-01T03:15:21Z")
\*yes, duration between dates / stages ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 1, 2024, 3:17am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/5 "2024-04-01T03:17:16Z")
Can you show me ideally how you would want it to look in the data editor table? ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 3:20am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/6 "2024-04-01T03:20:01Z")
![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/9/a94a29bd8d033f737a84bc1fbe025ab47ac65975.png) 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 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 1, 2024, 3:24am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/7 "2024-04-01T03:24:49Z")
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 ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 3:29am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/8 "2024-04-01T03:29:19Z")
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? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 1, 2024, 3:37am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/9 "2024-04-01T03:37:39Z")
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? ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 3:40am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/10 "2024-04-01T03:40:37Z")
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? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 1, 2024, 3:42am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/11 "2024-04-01T03:42:28Z")
Yes, that would be a good idea. ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 4:40am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/12 "2024-04-01T04:40:48Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [April 1, 2024, 5:00am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/13 "2024-04-01T05:00:27Z")
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 ---
**Author:** ![Matthew\_Viner](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matthew_viner/32/340_2.png) [@Matthew\_Viner](https://community.glideapps.com/u/Matthew_Viner)\ **Post date:** [April 1, 2024, 5:57am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/14 "2024-04-01T05:57:48Z")
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! ---
**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\ **Post date:** [April 8, 2024, 5:58am UTC](https://community.glideapps.com/t/date-between-stages-stages-captured-elsewhere-and-single-value-relates-latest/72203/15 "2024-04-08T05:58:27Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.