Find 7 th Consecutive worked Day
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi all,
How to find if there are 7 consecutive working days in a Row.
11111 | 8/01 | Yes |
11111 | 8/02 | Yes |
11111 | 8/03 | No |
11111 | 8/04 | No |
11111 | 8/05 | yes |
11111 | 8/06 | Yes |
11111 | 8/07 | Yes |
11111 | 8/08 | Yes |
11111 | 8/09 | Yes |
11111 | 8/10 | Yes |
11111 | 8/11 | Yes |
11111 | 8/12 | Yes |
11111 | 8/13 | yes |
11111 | 8/14 | No |
11111 | 8/15 | No |
11111 | 8/16 | No |
11111 | 8/17 | Yes |
11111 | 8/18 | Yes |
11111 | 8/19 | No |
Kindly help
- Labels:
- Date Time
- Dynamic Processing
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @dkma ,
Can you elaborate a bit more on what the columns in the table stand for?
Is 8/01 a date? So the 1st of August? If yes, for which year? Also, what yes.no stands for? Is it the desired output, or is it a flag of whether that employee has worked that day or not?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I take it that "Yes" means the person worked, and "No" means they didn't? Let's call that column [Worked].
I recommend using a multi row formula tool to create a ticker that starts with 1 on the first day worked, increases by 1 for every subsequent day worked, and resets back to 1 next time the person works after having taking some time off.
In the configuration of the Multi Row Formula tool, create a new field called Ticker. Set Value for Rows that Don't Exist to 0 or Empty. If you have multiple employees (more than just employee #1111), then Group By Employee.
The formula will look something like this:
if([Date]="No")
then 0
elseif(Row:[Worked]="Yes" and (Row-1:[Worked]="No" or Row-1:[Worked]=0))
then 1
elseif(Row-1:[Worked]="Yes")
then Row-1:[Ticker]+1
else Null()
endif
You may have to play with the formula a bit, but that's the general idea.
Then you can filter for [Ticker]>=7, and those will be the dates where the employee is on their 7th (or 8th or 9th, etc.) day of working.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@dkma
Batch macro should be a good match for this case.
