Skip to Content
avatar image
Former Member

how to find consecutive days?

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 10, 2009 at 04:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 10, 2009 at 03:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded