Year over Year comparison by weekday

[AlexSTeryx]

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 challenge of allocating incoming orders on weekends and holidays (e. g. through e-shop) to adjacent workdays.

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:

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

[AlexSTeryx]

Hi @RolandSchubert !
Thanks for your suggestion regarding this year over year matching of weekdays. 

I admit I've not yet understood the logic of the settings in the multi row formula tool entirely. Nonetheless, I see the result (1-Fri-1, 1-Fri-2, 1-Fri-3, ..., 1-Mon-1, 1-Mon-2, ..., 2-Fri-1, 2-Fri-2, ...) and recognise that this might be a steep in the right direction but cannot see yet the next step towards the final solution. I agree, the "not matched weekdays" will be an issue and I also wonder how to link the 1-Fri-1 of 2022 with the 1-Fri-1's of 2021 and 2020.

I tried to sketch again, what I would like to have at the end of the day: 

1. a table coming close to this. The tricky thing will be the matching code. Whether to use 0 and -1 for the days not matched in previous years or start wit 1 or 2 in the current year is probably just a question of the perspective.

This i would like to swoop to something like this:

Actually,  it must not necessarily be swooped. Maybe it is also possible to calculate the day by day delta and the delta for running total in the structure of the first table.

So, do you think the 1-Fri-1 can be used somehow as match code? possibly yes, but as you said, not matched days will cause trouble.

Best greetings an a nice Sunday

Alex

[AlexSTeryx]

Hi @RolandSchubert, 

after my replay on Saturday I realised, that the matching of 1-Mon-1 of current year with the corresponding 1-Mon-1 of the previous year will notwork in all cases. In the example given above for example Mon, Feb 7th 2022 (i.e. 2-Mon-1) matches with Mon, Feb 8th 2021, which, however, would be marked as 2-Mon-2.

Is that understandable what I mean?
Greetings

Alex

[RolandSchubert · Accepted solution]

Hi @AlexSTeryx ,

wouldn't it make sense to match by calendar week on a yearly basis instead on a monthly basis?

That would compare Mon, Jan 4th 2021 to Mon, Jan 3rd 2022 (cw 1 2021 <-> cw 1 2022) and Mon, Feb 8th 2021 to Mon, Feb 7th 2022 (cw 6 2021 <-> cw 6 2022).  

What do you think?

Best,

Roland

[AlexSTeryx]

Hi @RolandSchubert, 

That sounds promising! Matching the weeks Year over Year in a fist step and than the weekdays in a second could be a practicable approach.

I'll dive into that tomorrow and comeback to you. 

Thanks!
Alex

[AlexSTeryx]

Hi @RolandSchubert, 

finally I managed to thoroughly check your suggestion - and it seems to work! I calculated an indicator by concatenating Month, week and No. of Workday, transformed everything with the Cross Tab tool and checked the matching. For the years 2020 to 2022 it works!
Thanks!

Best Alex