Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #17: Month-over-Month Retention Rate

Simonvm9114
8 - Asteroid
 
mceleavey
17 - Castor
17 - Castor

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.



Bulien

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

 

 

dark_a
7 - Meteor

Hi all,

 

Great challenge. Here is my solution.

djodts
8 - Asteroid

Here's my solution

Pang_Hee_Choy
12 - Quasar
 
alozano
8 - Asteroid

Here is my solution.

 

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

 

Spoiler
challenge_17_alozano.jpg
atcodedog05
22 - Nova
22 - Nova

On a spree to binge complete weekly challenges

 

Good challenge to use batch macros

 

Spoiler
This is my solution

atcodedog05_0-1584900962782.png

 

GaneshBo
Alteryx
Alteryx

My Solution:

steven4320555
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.