Help Needed with Workflow for Accounting Entry Tagging

[AlteryxJorge]

Hello, Alteryx Community!

I hope you’re all doing well! I’m reaching out for assistance with creating a workflow for a specific situation I’ve been struggling with. I have two accounting entry systems: System Q, which has about 20,000 entries per month, and System P, which has around 300,000 entries per month.

At a high level, my goal is to categorize each entry based on the following definitions:

• D-1: Entries posted one day before the start of the relevant month.
• D+0: Entries entered during the relevant month.
• D+1, D+2, D+3, etc.: Entries entered in the subsequent month that still pertain to the previous month.

Here are more details on each system and my objectives:

Inputs: Two Accounting Systems

1. System Q:

   • Allows entries for the current month to start on the first day of that month. For example, for the February 2025 month-end, I can start posting entries on 2/1/2025.
   • I want to tag any entries posted in February 2025 that pertain to February 2025 month-end as "D+0."
   • Any entries posted in March 2025 that relate to February 2025 should be tagged as "D+1," "D+2," etc., depending on the specific business day in March they were posted.

2. System P:

   • Allows entries for the current month to start on the last day of the previous month. For example, for the February 2025 month-end, I can start posting entries on 1/31/2025.
   • I want to tag the 1/31/2025 entries as "D-1" in a new column I’ll call "D Plus Day" for now.
   • Entries posted in February 2025 that pertain to February 2025 month-end should be tagged as "D+0."
   • Lastly, any entries posted in March 2025 that relate to February 2025 should be tagged as "D+1," "D+2," etc., depending on the specific business day in March.

Please note that my real dataset from both accounting entry systems covers January 2024 through March 2025 and will continue going forward.

I've attached a simple Excel sheet that helps explain what I'm trying to do, using the February 2025 and March 2025 month-ends, along with the "D Plus Day" column I hope to create using Alteryx. I did this by hand, but with the volume I have in my real dataset, I need to use Alteryx.

The ultimate goal is to combine the data from these two systems, apply the appropriate tags, and then load it into Power BI to create simple visualizations that show how many entries, per system, were entered on D-1, D+0, D+1, etc., on a monthly basis.

I’ve attempted this a few times in Alteryx but haven’t been able to get it to work as intended. Any guidance or suggestions would be greatly appreciated!

Thank you in advance for your help!

[OTrieger]

@AlteryxJorge 
You can get from both Period End Date and Entry Creation Date the month as a number and then check if equal or not. If not then you are in D+0. If the Period End Date is bigger then it will be D+ and if it is smaller then it will be D-. 

Then you will need to have the next rule, if it is + or - take the day as a number.

All you have left to do is combining the D+ and D- with the number day.

All the above can be done with Formula tool, using IF statement.

Now for system Q you will need to have 1 rule that if month is bigger but it is the last day of the month that it should be D+0

[AlteryxJorge]

Thanks for replying. I didn't think of using the date as a number, good idea. I'll try it. 

For the next rule, I don't want to take the day as a number, I need to count business days, not just days. And since the first business day of each month isn't usually on the 1st day of the month, taking the day as a number won't work. 

[OTrieger]

@AlteryxJorge 
Good point on the business days. I guess that now you have some idea on how to get it solved.