Counting Successive Holidays

[DataIsFun]

Hi Guys,

I have a situation where I just need to count the number of successive days a person is away for. I have a feeling it involves a multi-row formula but that's a tool i'm rather unfamiliar with. In the output I just need one extra column to show the running **bleep** from one row to the next.

Many thanks for ant guidance at all

Here is a dummy sample of the data:

Name	Holiday	Leave_Date	DOW	Email
Lucas	Annual Holidays	15/05/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	16/05/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	17/05/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	18/05/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	19/05/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	20/05/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	21/05/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	22/05/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	23/05/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	24/05/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	25/05/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	26/05/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	27/05/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	28/05/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	29/05/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	30/05/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	31/05/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	01/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	02/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	03/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	04/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	06/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	07/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	08/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	09/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	10/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	11/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	12/06/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	13/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	14/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	15/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	16/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	17/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	18/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	19/06/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	20/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	21/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	22/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	23/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	24/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	25/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	26/06/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	27/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	28/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	29/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	30/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	01/07/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	02/07/2023	Sunday	Lucas@Gemini.com

[DataIsFun]

Hi Guys,

I have a situation where I just need to count the number of successive days a person is away for. I have a feeling it involves a multi-row formula but that's a tool i'm rather unfamiliar with. In the output I just need one extra column to show the running **bleep** from one row to the next.

Many thanks for ant guidance at all

Here is a dummy sample of the data:

Name	Holiday	Leave_Date	DOW	Email
Lucas	Annual Holidays	15/05/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	16/05/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	17/05/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	18/05/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	19/05/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	20/05/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	21/05/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	22/05/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	23/05/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	24/05/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	25/05/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	26/05/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	27/05/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	28/05/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	29/05/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	30/05/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	31/05/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	01/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	02/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	03/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	04/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	06/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	07/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	08/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	09/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	10/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	11/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	12/06/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	13/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	14/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	15/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	16/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	17/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	18/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	19/06/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	20/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	21/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	22/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	23/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	24/06/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	25/06/2023	Sunday	Lucas@Gemini.com
Lucas	Annual Holidays	26/06/2023	Monday	Lucas@Gemini.com
Lucas	Annual Holidays	27/06/2023	Tuesday	Lucas@Gemini.com
Lucas	Annual Holidays	28/06/2023	Wednesday	Lucas@Gemini.com
Lucas	Annual Holidays	29/06/2023	Thursday	Lucas@Gemini.com
Lucas	Annual Holidays	30/06/2023	Friday	Lucas@Gemini.com
Lucas	Annual Holidays	01/07/2023	Saturday	Lucas@Gemini.com
Lucas	Annual Holidays	02/07/2023	Sunday	Lucas@Gemini.com

[messi007]

@DataIsFun,

Please see below :

Attached the workflow. Hope this helps!

Regards

[Raj]

this is a continuous data for a single person .

Just want to understand are you going to prepare for one person only or there are multiple people

also there might be some working days between , what do you want to do in that condition

[DataIsFun]

Hi @Raj there is multiple people involved yes and to answer your other question it's all just holiday data so there will be no working days for anyone in between

[DataIsFun]

hey @messi007 thats looks ideal the only caveat being that I'm trying to get a running count so for instance if the dates are one after another it would be 1 2 3 4 and so for each row if that makes sense

[Raj · Accepted solution]

hope this help

[jrgo]

Is this what you're looking for? I used a MR Formula tool to see if the prior records date is exactly 1 day less than the active records date. If it is, then it increments the running count. If it's not, it restarts the count to 1. Note that this doesn't set any groupings in the MR tool so you may need to set that appropriately. And you may also want to sort before it goes into it in case AMP does something weird to the order.

[Yoshiro_Fujimori · Accepted solution]

@DataIsFun ,

So you will need two Multi-Row Formula tools.

One for count-up, another to detect the data break.

I hope this works for your case.

Sample Workflow

Multi-Row Formula 1 to count-up

Multi-Row Formula 2 to detect break