Challenge #86: Create a Macro that Generates Past Dates

Last week's solution can be found HERE!

Create a macro that generates a table with the date and the day of the week for all dates from a given starting date to today. The user should be able to select the start date.

Bonus 1: Add a selection that will allow the macro user to select whether or not to include weekends.
Bonus 2: Create a duplicate macro that's encrypted so that no changes can be made. Leave the first macro on your canvas unencrypted so other challenge-takers can observe how your macro was built.

challenge_86_start_file.yxzp

challenge_86_solution.yxmd

Macros

Advanced

Intermediate

Interface

Accepted answers

All comments

gnans19

Attached Macro

Spoiler

challenge_86_Gnans.yxmc

challenge_86_Gnans_Encrypted.yxmc

nick_ceneviva

Solution attached. Interested to see how people handled the skipping the weekends.

challenge_86_Ceneviva.yxmc

Kenda

First time attempting the weekly challenge! I attached my macro (both encrypted and not) to this post.

EDIT: When I am creating the macro, I can use this expression in the Generate Rows tool for the Initialization Expression: "%Question.StartDate%" to get the initial start date from the date interface tool. However when I run the macro, I noticed that the initialization expression somehow automatically gets changed to whatever I set the value to in my workflow configuration. Does anyone know why this happens?

Challenge 86 BarnesK Encrypted.yxmc

Challenge 86 BarnesK.yxmc

Natasha

Making the workflow was quick, but converting it into macro was a bit challenging as I am not using Interface tools a lot.

Spoiler

challenge_86_NK.yxmd

Fill_past_dates.yxmc

Claje

This one was relatively quick/easy for me - but mostly because I have a lot of exposure to dates and table creation.

Spoiler

I was able to get this done in four tools (excluding interface). Generate Rows->Formula->Filter->Output. Curious if there's a way to remove the filter tool with a more complex Generate Rows formula, and which option is more performant.

Challenge_86_Claje.yxmc

Challenge_86_Claje_Encrypted.yxmc

patrick_digan

Spoiler

Generate Rows > Formula > Filter

challenge_86_start_file.yxzp

NicoleJohnson

My solution! Ah, it feels good to finally be caught up on Weekly Challenges after my 3-week vacation... Which, if I use this spiffy new macro, I can use it to see a list of exactly which days I was on vacation! Cheers!

Spoiler

Included Bonus 1 & 2 in the attached workflow!

challenge_86_NicoleJohnson.yxmc

estherb47

I had hoped to leverage the Detour tools, but they were throwing errors at me, so went another route. This way took fewer tools in the end, but the Detours make more logical sense. If I get that method working, I'll post too.

Happy with the efficiency of only 3 main tools in the workflow + 2 interface interactions +1 action + 1 output.

And I learned something new and powerful!! Didn't know one could encrypt macros.

Spoiler

Generate Rows tool generates all of the dates between the chosen date and today

Then, a Date Time Parse tool creates the Weekday column (%A is the custom format)

Finally, a check box (when checked, macro will skip weekends), that feeds its value into a filter. When the checkbox is true, the weekend days are filtered out, and when false, all dates show. IIF took care of that for me. I wasn't sure if I could have just the field itself in the "value if false" so I used !IsNull([Day of Week]) to show all days of the week.

GenerateDates.yxmc

GenerateDates_Encrypted.yxmc

elamm43

My first macro and my first challenge!

challenge86_Lamm.yxmc

challenge86_Lamm_Encrypted.yxmc

JoeM

Welcome @elamm43!

EmanueleE

Hi guys!!! This is my solution!!!

Spoiler

#WC86 - EE - Macro1.yxmc

#WC86 - EE - Encrypted.yxwz

#WC86 - EE.yxmd

ankitthescorpion

This is my first attempt at an Alteryx Weekly Challenge. Please find attached my two macros for the weekly challenge. I have completed both the bonus challenges as well.

File 1 Non Encrypted and File 2 which is encrypted

Alteryx Weekly Challenge_86.yxmc

Alteryx Weekly Challenge_86Encrypted.yxmc

Niklas

Attached packaged canvas with post solutions

Spoiler

Challenge_86_attempt.yxzp

JoeM

Solution posted! After looking through this week's submissions, I found my solution to do two things that nobody else's did (albeit no more correct than anyone else's).

1) For the first time in awhile, I used this as an opportunity to use an Action tool that did not serve as an intermediary to the workflow and an interface tool. It merely existed to generate today's date in my Text Input tool.

2) I used the TS Filler tool. If you struggle with using generate rows, it's a nice easy tool to use.

estherb47

Welcome @elamm43!!

vishalgupta

Challenge_86_Macro_Vishal.yxmc

jdunkerley79

Nice challenge

Spoiler

days of week.yxmc

days of week_Encrypted.yxmc

Claje

@jdunkerley79

Spoiler

That Generate Rows formula is beautiful! I didn't even think of skipping weekends directly in step 1.

LordNeilLord

Blimey, well the workflow was eeeeasy but trying to get the action to update the filter tool was a terrible swine. I had to actually research how to do it! Thanks to @SeanAdams for his tool mastery posts

Spoiler

challenge_86_LNL.yxmc

challenge_86_LNL_Encrypted.yxmc

jamielaird

Here's my solution. Macros are my nemesis, so this was good practice.

Spoiler

challenge_86_JL.yxzp

SeanAdams

Ended up with a very similar solution to many others, but after having seen some of the solutions posted, I'm gonna go back and do this again using different tools

Spoiler

Soln86_Sean.yxmc

Soln86_Sean2.yxmc

ZoeyL1GL

solution attached

challenge_86_start_file_ZL.yxmd

LandonG

Solution attached.

challenge_86_start_file.yxzp

philipmannering

Solution attached.

Spoiler

challenge_86 - Generate past dates.yxzp

dsnyder

So I'm new to Alteryx and this is the first challenge I've responded to. It looks like everyone who has shared an image of their macro has used the generate rows tool, but I used the R tool, so I thought I'd share my solution.

Spoiler

Challenge_86.yxzp

ggruccio

Running it as an app helped me troubleshoot it. First time using Detour/Detour End.

challenge_86_gg_finish.yxzp

samN

Happy Little macro with all bonuses

challenge_86.yxzp

AndyBate

First time posting a challenge answer. Hopefully I've done this right.

Happy new year all.

challenge_86_Answer.yxzp

jasperlch

Solution attached.

challenge_86_JL.yxzp

JosephSerpis

Challenge Completed

Challenge_86_Joe_Serpis.yxzp

jamielaird

Here's my solution:

Spoiler

Calendar icon from https://www.flaticon.com/free-icon/calendar_138780#term=calendar&page=1&position=40

EDIT: I seem to have solved this one TWICE. My tracking spreadsheet is clearly not as perfect as I thought.

challenge_86_solution_JL.yxzp

kat

Thought my workflow was pretty cool, but then I saw other people be so much more efficient. At least I finally got to use detour - will be making much more use of that in future! But so much more efficient to just use the checkbox output in the filter... as well as having the date select go straight to the generate rows tool.

Spoiler

Challenge #86.yxzp

debn

Can you please post the files in version 11? My organization has not updated to 2018 yet.

thank you,

tcroberts

Here's my solution:

Spoiler

Not exactly optimal, since I generate all dates then drop if the box is checked, but it works well enough for pretty large ranges of dates so I was satisfied.

Seemed fairly simple for a challenge tagged Advanced though.

challenge_86_generatepastdates_completed.yxzp

atcodedog05

On a spree to binge complete weekly challenges
Adrenaline rush

Spoiler

This is my solution

challenge_86_atcodedog05_Solution.yxzp

tomrob

Spoiler

challenge_86_macro_tomrob.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335