We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!Submit Feedback
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).
I then used a formula to create the first of the month for the open dates. The formula is as follows:
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:
These were fed into a batch macro, with the first of the month date being the control parameter.
The macro looks like this:
The formula tool creates the following fields:
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:
The batch macro was the way forward.
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.
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.