Creating a holiday based output

[clarrock]

Hi,

Below is my input, it specifies if a Currency location has a holiday or not as well as if the settlement for the currency starts today (T0) or tomorrow (T1)

Input:

Todays Date	Currency	Settle Cycle	Holiday Date
2022-07-01	GBP	T0	2022-06-30
2022-07-01	EUR	T0	2022-07-01
2022-07-01	USD	T0	2022-07-04
2022-07-01	CHF	T1	2022-07-05
2022-07-01	CHF	T1	2022-07-06

The output alteryx shd run on daily basis is check those holidays that are today to future dates and see if the settlement cycle is T0 (start today) or T1 (start tomo). The end date would be decided based on when the next working day is for that Currency,

Note that the Currency settlement cycle doesnt matter if the holiday date and todays date match- so the output would be "Holiday Today".

The output ignore non working days ie Sat and Sun

Output required :

Currency	Settle Cycle	Start	End
EUR	T0	Holiday Today	Holiday Today
USD	T0	2022-07-01	2022-07-05
CHF	T1	2022-07-04	2022-07-07

Thanks,

Clar

[ArtApa]

Hi @clarrock - Couple of questions:

1) Why there is no GBP in the required output?

2) Can you please explain in more details what happens if there are more than one line for the same currency?

[clarrock]

@ArtApa 

1) GBP is not included because it's not equal to and is less than Today's Date in input (note that today's date changes with every day's run) - it was just for an example. We don't look at past holidays as the start date in the output can only be today (t0) or tomorrow (T1) if a non holiday

2) in the above situation there was more than one line for Chf ie a holiday on 05th July and 06th July so since it's a T1 currency we start tomorrow 04th while end date can't be 05th can't be 06th so moves to the next working day ie 07th.

 - Say there was also a Chf holiday on 04th (tomorrow) then the start would be 07th to end date 8th.

 - Say there was a holiday for USD on 5th as well, then since it's T0 the start would be 01st to end date can't be 4th can't be 5th so it's 6th which is the next non USD holiday

Let me know if you require a better explanation

[clarrock]

@ArtApa let me know if you have any further questions

[ArtApa]

Hi @clarrock - I don't understand how CHF start can 4th, if today is 1st and we need to add one day. Nevertheless, I created a workflow based on my understanding of your requirements:

[clarrock]

Hi @ArtApa thank you for your response, it works!. 2nd July is a Saturday so CHF starts on Monday 4th. 

The only issue i am facing here is that say there was a CHF holiday on 2022-12-26 the max Holiday would choose that as End.

How do i work around this?

[ArtApa]

Hi @clarrock - What CHF should look like, if it's a holiday on 2022-12-26? 

[clarrock]

Hi @ArtApa Chf should look the same ie start 4th to end 7th

Say, If there were no holidays 

T0 is start today to end tomorrow

T1 is start tomorrow to end day after tomorrow

Because of the holidays alteryx has to skip the days when there's a holiday. 

[clarrock]

@ArtApa let me know if I can clarify anything here. I'm unable to arrive at a solution to fix the holiday Calendar issue