Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #17: Month-over-Month Retention Rate

Highlighted
Alteryx Partner
 
Highlighted
Alteryx Certified Partner

Well, it's been a while, but as I watch a workflow running that will take around 23 hours to process I thought I'd have a look at the challenges again.

This was a bit mind-bendy until I got my head around the requirements (I'm old, shut up).

 

Spoiler
I first converted the dates to allow for datetime calculations:

mceleavey_0-1582819708747.png

I then used a formula to create the first of the month for the open dates. The formula is as follows:
DateTimeTrim([Open Date2],"month")

I then split the stream into two, one of which I grouped by the first of month dates and removed the one we did not need. The other stream I simply selected the Open and Close dates:

mceleavey_1-1582819842085.png

These were fed into a batch macro, with the first of the month date being the control parameter.
The macro looks like this:

mceleavey_2-1582819892992.png

The formula tool creates the following fields:

mceleavey_3-1582819970976.png

These are as follows:
1. Month - This updated by the control parameter and allows the month to be dynamically determined per row by the parameter. This is then used in the following calculations
2. Account Opened in 2 Year Period - This calculates if the account was created in the relevant 2 year period;
3. Closed in Month? - Was the account closed in the month determined.

The number of open and closed accounts are then determined, the % field is calculated by dividing Close by Open and the data is then output.
The batch will then loop through for every month giving the following results:

mceleavey_4-1582821454904.png

 

 The batch macro was the way forward.

 

M.

Highlighted
8 - Asteroid
Spoiler
kylemhabe_0-1582830947424.png
Macro:
kylemhabe_1-1582830969733.png

 

 

Highlighted
7 - Meteor

Hi all,

 

Great challenge. Here is my solution.

Highlighted
8 - Asteroid

Here's my solution

Highlighted
8 - Asteroid
 
Highlighted
8 - Asteroid

Here is my solution.

 

My macro isn't very clean but well it works:

 

Spoiler
challenge_17_alozano.jpg
Highlighted
12 - Quasar

On a spree to binge complete weekly challenges

 

Good challenge to use batch macros

 

Spoiler
This is my solution

atcodedog05_0-1584900962782.png

 

Highlighted
Alteryx
Alteryx

My Solution:

Highlighted
8 - Asteroid

 

I build a normal macro at the outset, and after check a youtube video https://www.youtube.com/watch?v=YIAbQGQ_Hkg, I immediately realised I should change the Month input into a control parameter. 

Spoiler
steven4320555_0-1585952647170.png


I have converted the text input tool as a macro-input, and realised I was wrong.

Some reflection: The control parameter tool is a special type of macro-input, which changes the value in the flow, once at a single run. The output is similar with an iterative macro, where results are unioned. The difference with iterative macro is the number of iteration is defined by another data stream, outside of the macro.