Count days worked in a row

[asamariaekrickl]

Hi all,

The requirement I have been given is to count the number of days staff work in a row.

In the screenshot below, I want to count the first 8 days where there is a value in Duration (below the Week day) and then skip 10/7 (where Duration is null) and continue counting from 11/07 and onwards until the next day where Duration is null.

Any ideas?

Thanks

[edwin]

a quick way would be to add the start date of the streak:

load Staff, if(peek('Date')=Date-1,peek('Start'),Date) as Start, Date;
load * inline [
Staff,Date
A,1/1/2021
A,1/2/2021
A,1/3/2021
A,1/10/2021
A,1/11/2021
A,1/12/2021
A,1/19/2021
A,1/20/2021
];

unfortunately the peek function might slow down your load if you have 100M+ records.

you can then show the number of days straight starting from specific dates