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: NameHolidayLeave_DateDOWEmailLucasAnnual Holidays15/05/2023MondayLucas@Gemini.comLucasAnnual Holidays16/05/2023TuesdayLucas@Gemini.comLucasAnnual Holidays17/05/2023WednesdayLucas@Gemini.comLucasAnnual Holidays18/05/2023ThursdayLucas@Gemini.comLucasAnnual Holidays19/05/2023FridayLucas@Gemini.comLucasAnnual Holidays20/05/2023SaturdayLucas@Gemini.comLucasAnnual Holidays21/05/2023SundayLucas@Gemini.comLucasAnnual Holidays22/05/2023MondayLucas@Gemini.comLucasAnnual Holidays23/05/2023TuesdayLucas@Gemini.comLucasAnnual Holidays24/05/2023WednesdayLucas@Gemini.comLucasAnnual Holidays25/05/2023ThursdayLucas@Gemini.comLucasAnnual Holidays26/05/2023FridayLucas@Gemini.comLucasAnnual Holidays27/05/2023SaturdayLucas@Gemini.comLucasAnnual Holidays28/05/2023SundayLucas@Gemini.comLucasAnnual Holidays29/05/2023MondayLucas@Gemini.comLucasAnnual Holidays30/05/2023TuesdayLucas@Gemini.comLucasAnnual Holidays31/05/2023WednesdayLucas@Gemini.comLucasAnnual Holidays01/06/2023ThursdayLucas@Gemini.comLucasAnnual Holidays02/06/2023FridayLucas@Gemini.comLucasAnnual Holidays03/06/2023SaturdayLucas@Gemini.comLucasAnnual Holidays04/06/2023SundayLucas@Gemini.comLucasAnnual Holidays06/06/2023TuesdayLucas@Gemini.comLucasAnnual Holidays07/06/2023WednesdayLucas@Gemini.comLucasAnnual Holidays08/06/2023ThursdayLucas@Gemini.comLucasAnnual Holidays09/06/2023FridayLucas@Gemini.comLucasAnnual Holidays10/06/2023SaturdayLucas@Gemini.comLucasAnnual Holidays11/06/2023SundayLucas@Gemini.comLucasAnnual Holidays12/06/2023MondayLucas@Gemini.comLucasAnnual Holidays13/06/2023TuesdayLucas@Gemini.comLucasAnnual Holidays14/06/2023WednesdayLucas@Gemini.comLucasAnnual Holidays15/06/2023ThursdayLucas@Gemini.comLucasAnnual Holidays16/06/2023FridayLucas@Gemini.comLucasAnnual Holidays17/06/2023SaturdayLucas@Gemini.comLucasAnnual Holidays18/06/2023SundayLucas@Gemini.comLucasAnnual Holidays19/06/2023MondayLucas@Gemini.comLucasAnnual Holidays20/06/2023TuesdayLucas@Gemini.comLucasAnnual Holidays21/06/2023WednesdayLucas@Gemini.comLucasAnnual Holidays22/06/2023ThursdayLucas@Gemini.comLucasAnnual Holidays23/06/2023FridayLucas@Gemini.comLucasAnnual Holidays24/06/2023SaturdayLucas@Gemini.comLucasAnnual Holidays25/06/2023SundayLucas@Gemini.comLucasAnnual Holidays26/06/2023MondayLucas@Gemini.comLucasAnnual Holidays27/06/2023TuesdayLucas@Gemini.comLucasAnnual Holidays28/06/2023WednesdayLucas@Gemini.comLucasAnnual Holidays29/06/2023ThursdayLucas@Gemini.comLucasAnnual Holidays30/06/2023FridayLucas@Gemini.comLucasAnnual Holidays01/07/2023SaturdayLucas@Gemini.comLucasAnnual Holidays02/07/2023SundayLucas@Gemini.com

Counting Successive Holidays

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

Workflow

Help

Accepted answers

Raj

hope this help

multi row formula result.yxmd

Yoshiro_Fujimori

@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

CountingSuccessiveHolidays.yxmd

All comments

messi007

@DataIsFun,

Please see below :

Attached the workflow. Hope this helps!

Regards

Multi-Row_20230511.yxmd

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

hope this help

multi row formula result.yxmd

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.

messi007.yxmd

Yoshiro_Fujimori

@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

CountingSuccessiveHolidays.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703