Having trouble calculating due dates

[RCern]

Hello everyone,

I have the attached workflow where I have a series of items that were scanned into a system.  I have a goal (number of days) for when those items should be due.  I need Alteryx to calculate the date due based on the number of days the goal represents without counting Saturday, Sunday and Holidays.  

In the attached workflow, I would expect Alteryx to tell me that the item scanned in on 5/1 would be due on 5/6 since the goal is 3 days and Saturday and Sunday need to be excluded.

I would expect the item scanned on 6/27 would be due on 7/7 given the holiday and weekends in between.  etc. etc.  

What am I doing wrong with the multi row formula that it is not looping and calculating correctly or is my whole approach wrong?

[jrlindem]

Have you considered joining the data to a date-dimension and using that to filter out weekend days and the holidays?  Then you could simply summarize up the number of days between the two periods.

[alexnajm · Accepted solution]

I think this is very possible, but you'll need to calculate all the days between the original date and a date maybe 30 days in the future, remove the weekends/holidays in between, and then count how many rows down based on the goal. Here's a solution that works for your data

[RCern]

@alexnajm  I understand the concept, but how do I roll it up to match the "Desired Results" table I show in the workflow?

@jrlindem I believe I am using a date dimension to filter out weekends and holidays, but the problem is having alteryx count the number of days without those to reach the goal.

[jrlindem]

My idea centers around filling in every date in between the start and end point.  Then with each of those days identified as "workday" vs "weekend" vs "holiday" you can flag each record as a '1' and then filter out the holiday and weekend..... then sum up the remaining 1's.  May not solve your need, but that's what my head was circling around.

Does that help any?

[alexnajm]

@RCern you can Summarize from the end, grouping by the columns you want to keep 😊 or use a Select tool!

[binuacs]

@RCern one way of doing it

[RCern]

@alexnajm  How can I set it so that the RecordID2 count starts over at 1 for each unique value in the "Type" column?

[alexnajm · Accepted solution]

I am not sure why you want that? To get your desired result, you just need a final summarize as mentioned

if you want a column that just lists "1" for each row, you can manually set it with a Formula or use a RecordID tool with the grouping set to each Type

Please accept the response as a solution since we have achieved the desired result. Thanks in advance!

[RCern]

@alexnajm  Thank you!