how to find consecutive days?

[Former Member]

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.

[Former Member · Accepted solution]

Hi

Why dont you try with time characteristics '0CALWEEK'. Add '0CALWEEK' to your infocube & map it with the date which you already have in the table. Now consider the your example for employee '100' -

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

by adding Calweek you will get - 100 24.2009 (that is 24 th week) 40 hrs.

Now in your query divide this 40 hrs (that is total working hours in week) with daily min working hrs. (In your case assume it 8 Hrs per day). So in query you will get number of working days in a week for each employee. From this you can easily find out how many leaves each employee has taken.

I think this is what you are looking for.

Regards

Sandeep

[Former Member · Accepted solution]

Thank you Sandeep. I awarded full points. That solves the problem.

[Former Member]

Does that solution deal with weekends? What about holidays? If you are calculating number of days off consecutive within a week you are missing those I believe.

Even if you skip holdays, which are a huge problem with calculating consecutive days off, then weekends themselves should be considered.

If you used 0CALWEEK would this senario be captured as 5 consecutive days off?

Week 12 - M - 8 hrs worked

Week 12 - T - 8 hrs worked

Week 12 - W - 8 hrs vacation

Week 12 - H - 8 hrs vacation

Week 12 - F - 8 hrs vacation

Week 13 - M - 8 hrs vacation

Week 13 - T - 8 hrs vacation

Week 13 - W - 8 hrs worked

Week 13 - H - 8 hrs worked

Week 13 - F - 8 hrs worked