Challenge #486: Prepare for Your Assignment Reviews!

[AYXAcademy]

Hi Community members,

A solution to last week’s challenge can be found here.

This challenge was submitted by Jifeng Qiu (@Qiu) , and inspired by a Community discussion started by @taschmies. We appreciate you both for bringing this case forward!

You have a series of assignment reviews scheduled for 2025/26. These reviews are important and it takes you at least two full working days to prepare for each of them. In some cases, multiple reviews may be scheduled on the same day and you must account for that in your preparation time.

Naturally, you won’t be working on weekends or public holidays. Based on the Review Schedule and the List of Holidays for 2025/2026, calculate your preparation days and mark them on the calendar:

• PrepDate1 = Review Date − 2 working days
• PrepDate2 = Review Date − 1 working day

Hint: Make sure to update the column names in your input files.

Once you have completed your challenge, include your solution file and a screenshot of your workflow as attachments to your comment.

Good Luck!

The Academy Team

Download Start File

Download Solution File

Source: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Work-backwards-from-a-given-da...

[alexnajm]

Done, although my output is different than the provided. I feel like I read the instructions correctly so I am happy with my answer but happy to see what others come up with

Spoiler

For the first record of 2025-10-03, shouldn't the answer be 2025-10-01 and 2025-10-02 since there are no holidays / weekends?

also the record 2025-12-02 has the same date as the prep dates...

[abacon]

I also got a different answer, but I feel good about it as well.

Spoiler

[jeprime]

Spoiler

Not sure how the Output Solution applies. Many of the answers show that both PrepDate1 and PrepDate2 are the same date (logically incorrect). However, I chose the easier path and did not seek an answer that the challenge requested - each "Prep Day" is a "Full Day", which meant that none of the other "Prep Days" can conflict. This poses a difficult challenge I chose to not partake in. Like those answering before me, I chose to simply find the two days before the review date that weren't weekends or holidays, only. 

Food for thought - how DID the solution table get created?

[Pilsner]

Interesting challenge! Ended up approaching it from multiple angles. I didnt quite get my answer to match but Im guessing thats down to my interprestation of the question. 

Spoiler

I took 3 seperate approaches, heres a brief breakdown of the thought preocess behind each method:

1) At first, I assumed no overlap was allowed, i.e if there were 3 reviews due on the same day, the preparation would need to start 6 days prior to the review date (2 days per review). However, this lead to a backlog of preparation days from 2024 so realised this was likely incorrect.

2) My next solution assumed that overlap was allowed therefore you could prep for more than one review on a given day. This solution thankfully didnt require an itterative macro but was still fun to build.

3) My final solution was a combination of the previous ideas. Only reviews happeing on the same day, could also be prepared for on the same day. All other revies had to have seperate designated preparation days.

Although my answer still didnt match I think I was along the right lines, plus there is always a chance I made a mistake early on, throwing all my solutions off. This challenge certianly made me think!

Workflow:

 
Macro:

 

 

[jeprime]

Like you, I decided to only find days that weren't holidays or weekends, barring determining if any prep-days overlapped with other prep-days. Still curious how they derived that solution. 

[Erin]

Like others, I have different answers than the posted output. But.....IDK it makes sense to me. 🤓

Spoiler

[jrlindem]

Oof, this one was a bugger.

I, like others, found the solution to be problematic.  I kind of ran into the same issue as the provided example solution where after a few rows the -1 and -2 dates weren't operating correctly.  I found, in my case, that when there were repeated ReviewDates in a row, the multi-row formula, if not configured well, was reading preceding dates of the exact same date in the span.  This is solvable (as seen in my example) but I admit that I did this in a down-and-dirty sort of way.  Not ideally how "clean" I typically like my workflows to operate.

Regarding what I believe the "spirit of the challenge" to be... I liked others observations that this sounded more like an iterative macro challenge where maybe there is a maximum capacity for preparation days and that needed to be removed out when there were multiple reviews needed on the same day.  That said... I did not build my solution to solve for this.  I simply provided the preceding two days (Skipping Non-Work Days or Weekends).

Here's my solution:

Spoiler

[lwolfie]

I came close to the output.  However, I couldn't quite figure out what the logic was for the multiple reviews due on the same day.  I had 13 days that didn't match.  

[Qiu]

@alexnajm @abacon @jeprime @Pilsner @jeprime @Erin @jrlindem @lwolfie 
Thank all very much for your insights.
I guess there was some mistake in my original solution in the original post.
I will take a close look by comparing mine with yours and hope find the most logical one.
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Work-backwards-from-a-given-da...

As @jrlindem interestingly pointed out, in the "spirit of the challenge", the more we discuss on the topic, which is one "close" to real bussiness problem, the more we can understand and learn, at least for me.

Thank you all again. 😁

Ps: @AYXAcademy Thank you for accepting my weekly challenge idea.