Counting consecutive days

[Deano478]

Hey folks,

I have a bit of an issue I'm struggling with that could possibly be simple I'm not sure. What I have is a sample dataset as seen below that contains leave for dummy individuals. What I am trying to do is based on the Leave_Date column is get a count of how many consecutive days a person if off for.

Below is a sample of the data albeit cleaned up to look nice.

Hopefully that makes sense

Name	ID	Leave_Date	Leave_Type	Approved	Year	Email
Dean	1	18/04/2023	Annual Leave	1	2023	dean@test.com
Dean	1	19/04/2023	Annual Leave	1	2023	dean@test.com
Dean	1	20/04/2023	Annual Leave	1	2023	dean@test.com
Dean	1	21/04/2023	Annual Leave	1	2023	dean@test.com
Dean	1	24/04/2023	Sick Leave	1	2023	dean@test.com
Dean	1	25/04/2023	Sick Leave	1	2023	dean@test.com
Tom	2	26/04/2023	Sick Leave	1	2023	Tom@test.com
Tom	2	27/04/2023	Sick Leave	1	2023	Tom@test.com
Tom	2	28/04/2023	Sick Leave	1	2023	Tom@test.com
Tom	2	29/04/2023	Annual Leave	1	2023	Tom@test.com
Tom	2	02/05/2023	Annual Leave	1	2023	Tom@test.com

[Luke_C · Accepted solution]

Hi @Deano478 

I think the multi-row formula tool could help, how does something like this look? It uses the date time functions to check if the row is 1 day more than the previous record. This might get hairy depending on how you want to account for weekends. 

[FinnCharlton · Accepted solution]

Hi @Deano478, here's a way to do it with a multi-row formula:

[Clifford_Coon · Accepted solution]

Hi Deano,

A multi-row formula works here.

convert to Alteryx date, sort and use multi-row formula.

 

[Deano478]

@Luke_C @FinnCharlton  and @Clifford_Coon  much appreciated for you taking the time to gimme a hand the use of the Multi Row formula worked like a charm. Its definitely a tool I need to get more familiar with