Counting days within a 7 day period

Question

I need to be able to count how many days somebody works within any 7 day period. So I have created an excel spreadsheet with data (attached) and the 'RESULT' (ie. the output of this workflow) needs to tell me how many times the person works in the next 7 days (including that day). For example, if you look at the 4th data row is displays D0004, 14/01/2020, Y and the result is 6 because that employee worked (6 days in the week starting with date 14/01/2020 to 21/01/2020 as he didnt work on 18/01/2020). If we look at the 12th data entry which displays D004, 24/01/2020, Y and the result is 4. This is because in the week 24/01/2020 - 31/01/2020 the employee works 4 of the days.

Employee Data (Alteryx).xlsx

T_Willins · Accepted Answer

Hi @Jake_Pajer,

Yes, Alteryx can do this using a Multi-Row Formula tool.  Since the maximum number of rows ahead that needs to be compared is 6 the tool can be set to compare each line ahead of the current row up to 6 and add them together (+1 for the current row).  Grouping by Employee ID keeps the comparison only to the same employee.  The Select Tool at the end is only to bring forward only the fields shown in your example.