Allocating Orders on work free days to workdays and Year over Year comparison by weekday

[AlexSTeryx]

Hi Alteryx Community,

I am setting up a Year over Year Report for Ordered Net Sales and am facing two challenges:

1. allocating incoming orders on weekend and holiday (e. g. through e-shop) to adjacent workdays and
2. regarding the dates which to compare.

Allocation Orders

The general rule for the allocation of the incoming orders is that they are allocated to the following workday. Only if there is no workday following – because of month end – they are routed to the preceding workday.

The challenge results from the variety in length of work free periods – normal weekend two days, eastern for example four days. An additional challenge occurs, when such a prolonged weekend falls on change from one month to the next.

Two Examples:

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 both the allocation issue and matching of weekdays issue.

Thanks

Alex

[RolandSchubert]

Hi @AlexSTeryx ,

my approach would be to create a kind of "matchcode" by calculationg the "number of weekday in a month including only workdays". You can use a Multi-Row Formula tool for this task. Next step would be to build a matchcode from Month, Weekday Name (or number) and weekday# (as calculated before).

There will be some additional issues (how to handle "not matched weekdays" (e.g. 5 Mondays in 2021, only 4 in 2022), maybe necessary to find a solution for that.

 What do you think?

Best,

Roland

[AlexSTeryx]

Hi @RolandSchubert ,

thanks for your immediate reaction.

I‘ll have a closer loo on it on Monday when I‘m back at my „Alteryx PC“ and come back to you.

Do you have by any chance also a suggestion for the first part of my post?

Best

Alex

[RolandSchubert]

Hi @AlexSTeryx ,

I think, I also found an approach for your first problem. My approach would be to generate a list of "corrected order dates" for each possible order date, i.e. find the next business day for each date. 

The logic behind this is:

- generate a list of all dates in range (2020-01-01 to 2022-12-31)

- multiply the list for each sales org

- join with list of holidays -> holiday = "no workday" (country specific)

- find weekend days and set them to "no workday"

- sort in descending order (start with last date)

- for each date check and set "corrected order date"

Now it should be possible to join this list with input data. Difference in holidays between AT and DE results in different order dates.

Let me know if it works for you.

Best,

Roland

[AlexSTeryx]

Thanks @RolandSchubert that you keep on it!
I reckon, it was not so clever addressing two issues in one post. Hence, I decided to re-post them separately and I'll answer you in the new posts later:

->  Year over Year comparison by weekday

-> Matching weekend and holidays to workdays

Best 

Alex