Excel Function Workday and Networkdays in Alteryx

[Gr4c3Sult]

Hi Community!

I am automating a large repeatable report that is hundreds of thousands of rows.

I have searched and looked at several workflow solutions proposed for Workday and Networkdays.. but my date in Workday can be any working day and my start date in Networkdays can also be any day of the week.  I haven't seen any solutions that will accommodate my scenario..  If there is one out there that I have missed please share it with me 🙂  

Steps to bring out the AP Date is put the formula =WORKDAY(Vendor Paid Date,-2, Holidays)

Steps to bring out the “Days 2 Pay Put the formula =Networkdays(Accounting date, AP Date, Holiday Table)-1

Any help is greatly appreciated!!

Grace

[apathetichell]

O.k. - you probably want to post some data including a holiday file so  I can mock up a workflow... but basically:

in alteryx you'd probably do this a bit differently. I'd start by having a list of holidays in date format (just like you need in excel)... Then for your networkday function you create a range of dates using generate rows for the days between accounting date and ap date. You then use a join filter out the holidays (ie the matched entries). You then use summarize to take a count. Then you subtract 1. Voila.

you'll probably use something similar where you create a range - from min to max. use a join to dump the holidays. and then your workday is the date in your row-2 in a multi-row formula.

[RolandSchubert]

Hi @Gr4c3Sult ,

an approach for your problem is to generate a list of dates, starting with your start_date and adding/subtracting the number of days (second param in function). The next step is to remove weekend days (using the DateTimeFormat function you can identify the weekday and use a Filter tool to select only working days.

To also remove holidays, you have to provide a list (like the range in Excel), You can e.g. use a Join tool and keep only the dates from your generated list, that are not in the holiday list ("L" output).

WORKDAY return the last workday date in the range, you can use a Summarize tool using "MAX" to find it, NETWORKDAYS return the number of days, a Count tool will help.

Please let me know, if this meets your requirement?

Best,

Roland 

[mst3k · Accepted solution]

you could use the generate rows tool to produce a row for each day between START and END which is a straightforward approach that could work perfectly for you. but in the event you have a significant amount of data and/or years between the dates, that could be very inefficient and generate many many times the size of your original dataset in processing. if your dates are 1 year apart, you'll have 365 rows for each row you started with, if you have 1 million records you'll make poor old alteryx crunch 365 million rows

if that's a concern at all, then i have a more efficient approach for that scenario. i started by calculating how many full weeks are between the dates, and we know there should be 5 weekdays in each of those, plus adjusting for if the start/end is on a weekend. then for any remaining partial week to get to the end date, we can generate rows just for those days (which will never be more than 7 per original record, so in the above example alteryx would only have to crunch 7 million records instead of 365 million). then throw out the saturday/sunday in that partial week.

finally, merge in an optional list of holidays, check if they're between the start/end, and adjust for those

[Gr4c3Sult]

@apathetichell ,

Thank you for the suggestion.  I have attached an example of the dates and holidays.  I have played with multiple ways, and I am failing 😞 your help is appreciated.

[Gr4c3Sult]

@RolandSchubert 

I don't think that will work.. I have attached a sample of my data and expected results.  Each row has different dates starting on different days of the week.  How does alteryx know to assign 6 and 7 to Saturday and Sunday?

Appreciate your help!!

[apathetichell]

@mst3k- you can avoid the 365 million row conundrum by using a macro.

@Gr4c3SultAlteryx can extract saturday or sunday (or 6/7) from a date using datetimeformat([date],"%u") provides 6 or 7 for Saturday or Sunday. - if you're going to have 1,000,000 or so rows I'd build as a macro since it makes more sense.

[mst3k]

the macro will still have to generate and crunch 365 million rows in that scenario. a straight generate rows tool as a solution works fine, but it will end up generating a row for every day between the dates of every record in your data, so if you have 1 million rows a year apart, alteryx will have to generate 365 million rows whether it's in 1 fell swoop or chunked out into macro runs unfortunately. you won't have 365 million rows at the end - but alteryx will still have produced them during the run and it could potentially cause long run times and heavy processing usage

[mst3k]

not sure which dates you're comparing, so i assumed acctg date and payment date. you can alter it if it's supposed to use other dates.

there's nulls in my results because there are instances where the payment date is before the accounting date. so maybe i used the wrong dates, you should be able to swap them out in all the tools as needed.

connect your file back into it again on your end

[oly]

Check the example over here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-add-business-days-to-the-date-f...