Holiday run

[mhtomines]

Hi Team,

Need your help with my workflow. What I'm trying to do is to get the date after a holiday. For example, July 4 (Independence day) alteryx will get July 3 dates. My workflow current formula is getting previous day and excluding weekends.

iif(DateTimeFormat(datetimetoday(),"%A")="Monday", DateTimeAdd(datetimetoday(),-3,"days")= [first_billing_date], DateTimeAdd(datetimetoday(),-1,"days")=[first_billing_date])

[FinnCharlton]

Hi @mhtomines, your formula is looking good, you just need to take out the "=[first_billing_date]" in the last two clauses. Alteryx doesn't require this syntax to change the field, you just need to set the [first_billing_date] as the target column in your formula tool:

Try this formula:

iif(DateTimeFormat(datetimetoday(),"%A")="Monday", DateTimeAdd(datetimetoday(),-3,"days"), DateTimeAdd(datetimetoday(),-1,"days"))

[mhtomines]

Hi @FinnCharlton appreciate your response however I need to add in my workflow how to get data if it is after a holiday. My current formula only get previous day or after weekend. For example, the July 4 holiday may data is dated July 3 but I will run the report by July 5. I need to get first billing date July 3.

[jdminton]

@mhtomines Your formula only looks at today's date due to the DateTimeToday() expression. [Date] is the field containing the dates of holidays to test. 

Change it to the below for the day before a holiday:

iif(DateTimeFormat([Date],"%A")="Monday",DateTimeAdd([Date],-3,"days"),DateTimeAdd([Date],-1,"days"))

And after the holiday would be:

iif(DateTimeFormat([Date],"%A")="Friday",DateTimeAdd([Date],+3,"days"),DateTimeAdd([Date],+1,"days"))

 

I added 7/3 since it's a Monday to test when the day follows a weekend and 7/7 to test the day before a weekend.

 

[mhtomines]

Hi @jdminton I'm trying to do it in a filter tool, for example July 25 is a holiday and I like to get the data of July 24.

account_no	client_name	first_billing_date
222222222	A client	7/24/2023 0:00
333333333	B client	7/24/2023 0:00
444444444	A client	7/24/2023 0:00
555555555	B client	7/21/2023 0:00
666666666	A client	7/21/2023 0:00

[jdminton]

@mhtomines Yes, but you mentioned that you also wanted the date after. The workflow I attached gives you both. You would need to add logic to determine whether you want the day before or day after. Can you describe the scenario where you would need the day after instead of the day before?

Also, once you have the fields in a formula tool, you can reference the fields in your custom filter.

[mhtomines]

My logic would be:

For a normal run: Get Previous day or if it's Monday get last Friday data

If there's a holiday: Get previous day before holiday or if the Holiday falls on a Friday get last Thursday data

[jdminton]

@mhtomines I think I understand now. If your workflow does not already have holidays identified, you would need to join in a list of dates that qualify as holidays first. Since you are just wanting the day before, I've called this "Run Date" as it sounds like you're running reports. You can definitely rename if needed. The filter tool cannot do the logic that you are asking because you would have to introduce the logic of what qualifies as a holiday. I'm attaching a workflow that should help. The first part is to generate days to show how it works. The next step joins on a list of holiday dates that can be modified. The next is a formula to identify any day to be skipped. Finally a multirow tool counts backwards to find the latest date that isn't a weekend or holiday. I've set the formula for going back as far as 5 days. If you expect more holidays or weekend days in a row, you would need to extend the formula.