Hi All, I want to count the number of consecutive days for each attendance status for a specific employee. So here an example of the data: Emp IdEmp NameProject IdProgramDateSchedule Status111111Max Mustermann55Test Progrma07/01/2021Off111111Max Mustermann55Test Progrma07/02/2021Work111111Max Mustermann55Test Progrma07/03/2021Work111111Max Mustermann55Test Progrma07/04/2021Work111111Max Mustermann55Test Progrma07/05/2021Work111111Max Mustermann55Test Progrma07/06/2021Work111111Max Mustermann55Test Progrma07/07/2021Work111111Max Mustermann55Test Progrma07/08/2021Off111111Max Mustermann55Test Progrma07/09/2021Off111111Max Mustermann55Test Progrma07/10/2021Work111111Max Mustermann55Test Progrma07/11/2021Work111111Max Mustermann55Test Progrma07/12/2021Work111111Max Mustermann55Test Progrma07/13/2021Work111111Max Mustermann55Test Progrma07/14/2021Work111111Max Mustermann55Test Progrma07/15/2021Work111111Max Mustermann55Test Progrma07/16/2021Work111111Max Mustermann55Test Progrma07/17/2021Off111111Max Mustermann55Test Progrma07/18/2021Off111111Max Mustermann55Test Progrma07/19/2021Work111111Max Mustermann55Test Progrma07/20/2021Work111111Max Mustermann55Test Progrma07/21/2021Work111111Max Mustermann55Test Progrma07/22/2021Work111111Max Mustermann55Test Progrma07/23/2021Work111111Max Mustermann55Test Progrma07/24/2021Off111111Max Mustermann55Test Progrma07/25/2021Off111111Max Mustermann55Test Progrma07/26/2021Unscheduled111111Max Mustermann55Test Progrma07/27/2021Unscheduled111111Max Mustermann55Test Progrma07/28/2021Unscheduled111111Max Mustermann55Test Progrma07/29/2021Unscheduled111111Max Mustermann55Test Progrma07/30/2021Unscheduled111111Max Mustermann55Test Progrma07/31/2021Unscheduled So as on output I want to have a matrix where under ROWs I will put - > Emp Name/Schedule Status and under values - the max consecutive days for each status. So the output will be for this case: Name/ScheduleStatusMax ConsecutiveMax Mustermann "-Work"7"-OFF"2"-UNSCHEDULED"6 I would really appreciate if someone can help me with this - at a first glance it looks rather easy, but turns out to be quite hard, at least for me. Thanks in advance!! BEst regards,Ivan

Helper III

5 years ago

Solved

Count consecutive days on a specific status

Hi All,

I want to count the number of consecutive days for each attendance status for a specific employee. So here an example of the data:

Emp Id	Emp Name	Project Id	Program	Date	Schedule Status
111111	Max Mustermann	55	Test Progrma	07/01/2021	Off
111111	Max Mustermann	55	Test Progrma	07/02/2021	Work
111111	Max Mustermann	55	Test Progrma	07/03/2021	Work
111111	Max Mustermann	55	Test Progrma	07/04/2021	Work
111111	Max Mustermann	55	Test Progrma	07/05/2021	Work
111111	Max Mustermann	55	Test Progrma	07/06/2021	Work
111111	Max Mustermann	55	Test Progrma	07/07/2021	Work
111111	Max Mustermann	55	Test Progrma	07/08/2021	Off
111111	Max Mustermann	55	Test Progrma	07/09/2021	Off
111111	Max Mustermann	55	Test Progrma	07/10/2021	Work
111111	Max Mustermann	55	Test Progrma	07/11/2021	Work
111111	Max Mustermann	55	Test Progrma	07/12/2021	Work
111111	Max Mustermann	55	Test Progrma	07/13/2021	Work
111111	Max Mustermann	55	Test Progrma	07/14/2021	Work
111111	Max Mustermann	55	Test Progrma	07/15/2021	Work
111111	Max Mustermann	55	Test Progrma	07/16/2021	Work
111111	Max Mustermann	55	Test Progrma	07/17/2021	Off
111111	Max Mustermann	55	Test Progrma	07/18/2021	Off
111111	Max Mustermann	55	Test Progrma	07/19/2021	Work
111111	Max Mustermann	55	Test Progrma	07/20/2021	Work
111111	Max Mustermann	55	Test Progrma	07/21/2021	Work
111111	Max Mustermann	55	Test Progrma	07/22/2021	Work
111111	Max Mustermann	55	Test Progrma	07/23/2021	Work
111111	Max Mustermann	55	Test Progrma	07/24/2021	Off
111111	Max Mustermann	55	Test Progrma	07/25/2021	Off
111111	Max Mustermann	55	Test Progrma	07/26/2021	Unscheduled
111111	Max Mustermann	55	Test Progrma	07/27/2021	Unscheduled
111111	Max Mustermann	55	Test Progrma	07/28/2021	Unscheduled
111111	Max Mustermann	55	Test Progrma	07/29/2021	Unscheduled
111111	Max Mustermann	55	Test Progrma	07/30/2021	Unscheduled
111111	Max Mustermann	55	Test Progrma	07/31/2021	Unscheduled

So as on output I want to have a matrix where under ROWs I will put - > Emp Name/Schedule Status and under values - the max consecutive days for each status. So the output will be for this case: