Creating monthly funding and invoice date

Question

I am trying to find the best solution for my workflow that needs to populate dates related to funding payments. The requirement by our AP department is that an invoice date is one date before funding, but a unique code is created using the funding date (ex. invoice date 8/15/23 but unique code would be something like SU08162023-1, etc.). It would not be the same date each month but funding generally would be the closest Wednesday before the 20th of each month (ex. August 2023 have funding 8/16 and invoice 8/15 but October 2023 would have funding 10/18 and invoice 10/17). Is there a formula or tool I could write to accomplish this? Or am I better off getting an input file with these dates layed out in a table to use for an import to populate what I need in this workflow?

denvic29 · Answer

Apologies for the delay! This did work, however, I found out that it changes based on some holidays, so I'm working on another solution but hope to be able to leverage something like this.

TheMattLeonard · Answer

It's definitely possible to create the dates in Alteryx if your rules are consistent. Here's what I would do:

1. Create a text input with a start and end date. I did one field called "Starting Date" with a value of 2023-08-01 and another field called Ending Date with a value of 2025-12-31

2. Connect a generate rows tool. You're going to create a new field, call it "Day" (make sure its data type is "Date"). Your Initialization Expression is going to be [Starting Date], your Condition Expression is going to be [Day]<[Ending Date] and your Loop Expression is going to be DateTimeAdd([Day],1,"Days").

This should generate a list of all days between your starting and ending dates.

3. Connect a formula tool. We are going to to three different expressions here.
            Day of Month: DateTimeDay([Day])
            Month: DateTimeMonth([Day])
            Year: DateTimeYear([Day])

4. Next, attach a DateTime tool (in parse pallet). Select "Date/Time format to string". Select [Day] as your field to convert. Specify the new column name as "Day of Week". Select "day, dd-Month, yyyy" as your new format.

5. Use a filter tool and filter to just Wednesdays. Use another filter tool and do Day of Month is less than 20

6. Use a summarize tool. Group by Month and Year, Max for the [Day] field. Rename it to "Funding Date"

7. Use a formula tool to create new column called Invoice Date. Your expression should be DateTimeAdd([Funding Date],-1,"Days")

8. Optional, sort by Year and then by Month.

I believe this should do the trick. Let me know if you have any questions.