Counting consecutive days

Question

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

NameIDLeave_DateLeave_TypeApprovedYearEmailDean118/04/2023Annual Leave12023dean@test.comDean119/04/2023Annual Leave12023dean@test.comDean120/04/2023Annual Leave12023dean@test.comDean121/04/2023Annual Leave12023dean@test.comDean124/04/2023Sick Leave12023dean@test.comDean125/04/2023Sick Leave12023dean@test.comTom226/04/2023Sick Leave12023Tom@test.comTom227/04/2023Sick Leave12023Tom@test.comTom228/04/2023Sick Leave12023Tom@test.comTom229/04/2023Annual Leave12023Tom@test.comTom202/05/2023Annual Leave12023Tom@test.com

Luke_C · Accepted Answer

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 Answer

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

Clifford_Coon · Accepted Answer

Hi Deano,

A multi-row formula works here.

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