Multi row Formula

[vchauhan011]

Hello,

I have a dataset which has multiple IDs. each ID can have more than one entries. It looks as below. 

ID	Client name	date trantioned	Transitioned by	transitioned From Status	Transitioned To Status
1	ABC	11/06/2021 15:50:43	Dane	Created	In Progress
1	ABC	11/07/2021 21:10:04	Tom	In Progress	On Hold
1	ABC	11/07/2021 23:30:31	Tom	On Hold	Completed
2	DEF	11/06/2021 08:43:06	Ryan	Created	In Progress
2	DEF	11/07/2021 10:54:16	Sam	In Progress	On Hold
2	DEF	11/08/2021 14:48:19	Sam	On Hold	In Progress
3	GHI	11/03/2021 12:26:38	Don	Created	In Progress
3	GHI	11/03/2021 12:26:38	Don	In Progress	On Hold
3	GHI	11/03/2021 12:26:38	Don	On Hold	Completed
4	JKL	11/08/2021 13:42:53	Nicole	Created	In Progress

I want to achieve two things here. 

1) My main objective is to see which IDs are In Progress transitioned to status. Output should look similar to the below table. 

2	DEF	11/22/2021 14:48:19	Sam	On Hold	In Progress
4	JKL	11/08/2021 13:42:53	Nicole	Created	In Progress

2) I created a workflow where I was able to filter out a lot out extra entries however I am not able to filter out certain IDs (for example ID 3). Due to a technical glitch, program is creating different entries for certain IDs where all the information is same (such as trantioned date and transitioned by) other than transitioned from and transitioned to status. This glitch is creating different entries for particular IDs where it shows same person transitioned the ID from and to all the existing statuses on the same time. 

Thank you very much in advance for your help!

[Luke_C]

Hi @vchauhan011 

Here's my understanding: You want to see the clients who's latest update is to 'In Progress'. 

To do this, I don't think you need a multi-row formula. What you can do is sort the data by date (after converting the timestamp to a datetime field), take the first value (most recent), and filter to see which were moved to in progress.

[vchauhan011]

Hi @Luke_C 

Thank you for providing a solution! Right, I want to see clients who's latest update is to 'In Progress'. 

I used the same tools and process as you suggested in my current workflow however, it is still capturing unwanted entries for certain IDs. These entries are created due to the glitch in the software. Since all the entries for these IDs (ID "3" in the input example) have same date and time stamp, sampling tool is capturing them instead of filtering them out. Can you please advise how can I fix it? Thank you! 

[AZuc · Accepted solution]

@vchauhan011 

I've worked in a similar case. This is a case of ticket management, where the ticket always starts as Created and then the status can be changed to In Progress or On Hold until it reaches the Completed status, right?

What usually happens to have 2+ transitions in the same minute is that the REP that is working the ticket needs to close it (status = Completed) but also needs to add some extra information before closing. When the ticket is closed they can't changed it. So, they move the status from In Progress to On Hold to stop counting time for SLA, and then close the ticket.

In this case, we can take @Luke_C approach as the base and treat the exception (having 2+ transitions in the same minute).

I've built an example that removes all IDs that reached the Completed Status for the transitions that occurred in the latest minute.

See if it helps

[vchauhan011]

Hi @AZuc ,  Thank you for providing the solution. It worked perfectly!