Hi I have a requirement, where I need to find all employees who have taken 5 consecutive days off in one week.
I am trying to build a query in BI, which can identify these records, but of no avail. I am wondering if I can do this in visual Composer. My data output currently looks like this in a table.
Employee number calendar day time-off hours
100 6/8/2009 8
100 6/9/2009 8
100 6/10/2009 8
100 6/11/2009 8
100 6/12/2009 8
101 6/1/2009 8
101 6/5/2009 8
I like to loop through this table, somehow, and identify that employee 100, has 5 consecutive days off, but not 101.
I like to filter those employees who do not have 5 consecutive days off, and just show those who have 5 days off, like employee number 100. My output should look like, this
Employee number calendar day time-off hours
100 6/8/2009 8
100 6/9/2009 8
100 6/10/2009 8
100 6/11/2009 8
100 6/12/2009 8
Any ideas will be greatly appreciated. I am trying out dynamic expression, but cannot find a way to access the first row of data, while processing the second row.