alteryx Community

# Alteryx Designer Discussions

## Find 7 th Consecutive worked Day

8 - Asteroid

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

5 REPLIES 5
15 - Aurora

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?

7 - Meteor

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.

16 - Nebula

I would suggest:

First, parse the dates

You can then filter the days not worked

Then final sort into ID, Date ascending

Finally a multi-row formula tool can confirm that the last 7 days were all worked for an ID:

Have attached a quick sample

16 - Nebula

Stylistically I don't ever use more than say 2 in the multi-row formula. I don't really know why.  I like a Tile tool approach/unique value approach.

17 - Castor

@dkma
Batch macro should be a good match for this case.

Labels