Matching weekend and holidays to workdays

Question

Hi Alteryx Community!

Preliminary note: originally I posted this together with another topic in one post but came to the conclusion it is better to split them up (the second topic you might find here).

I am setting up a Year over Year Report for Ordered Net Sales and am facing the following challenge: how to match the first workday of a month of current year with the corresponding same weekday (not workday) in the previous years(s).

Regarding the comparison we run two concepts:

Generally, we want to compare on a daily basis the current month of the current year with the corresponding months of the two previous years.

Concept 1 compares Weekday vs. corresponding weekdays and concept 2 compares workdays vs workdays.

Finding the corresponding workday shouldn’t be an issue since I have the running total for the workdays.

But my challenge is how to identify the respective matching days in the previous years. Especially for the match of weekdays.

In the attached workflow I

* Marked the workdays and weekends/holidays,
* Calculated the running totals for workdays per year, quarter and month and
* (in the last step) managed to identify the weekday (Mon; Tue, etc.) of the first workday per month in 2022. But how to identify the corresponding weekdays in 2021 and 2020?

I would appreciate any help solving this allocation issue.

Thanks

Alex

PS: to this I already received one replay from @RolandSchubert which you'll find here and to which I'll answer below.

ax

DateCalcCommunity_v01.yxzp

AlexSTeryx · Accepted Answer

Hi @RolandSchubert,

many thanks for your suggestion! Unfortunately the workflow does not run on my machine -

As far as I can decipher from the screen shots it might be a suitable solution. However, meanwhile I figured out a solution myself:

Having the workday (1) and no-workday (0) marker and the running total for workdays (grouped by Sales Org, year and month) i found an easy solution using the formula tool.

Just add a new field with the formula:

iif([Workday] = 1, [RunTot WoD / YM], 
iif([Workday] = 0 and [RunTot WoD / YM] > 0, [RunTot WoD / YM], 
iif([Workday] = 0 and [RunTot WoD / YM] = 0, [RunTot WoD / YM] + 1, 99)))

When the day is a workday and marked with "1", I take the value from the running total of workdays, is the day a weekend day or a holiday and marked with "0" I have two options

* there is a preceding workday and the running total is >0. Since the running total for teh weekend days is identical with the running total of the preceding workday, I take this.
* there is no preceding workday, and the running total of the workdays is so far is therefore 0. In this case I add "1" to the running total (0+1 = 1) and match the weekend days to the first workday of the month.

In this example the last weekend of May 2020 has no workday following and is matched to the preceding 19th workday of the month Mai 2020. 
The following Whit Monday has no preceding workday (since it was June 1st in 2020) and must be matched in this case to the following workday.

Best Greetings

Alex