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 | |
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 |
Solved! Go to Solution.
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
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
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
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.
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