Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Macro Iteration Help

msnyder
5 - Atom

Hello everyone-

 

I've been working on building an iterative workflow to calculate Present Values at future points in time for a given set of cashflows but am not able to figure out macros just yet. 

 

I attached a simple example of what I'm trying to accomplish and make more efficient.  Any help working through this would be much appreciated.

5 REPLIES 5
Luke_C
13 - Pulsar

Hi @msnyder,

 

See attached. To make this work I did the following:

 

  1. Before the macro, I used a formula tool to add EFF_DATE to the data, hardcoding 2021-01-01. You can change this at any point.
  2. Within the macro itself, we perform the filter based on OUTDATE and EFF_DATE, summarize, and output that data.
  3. At the same time, the unsummarized data goes to a formula tool that adds 3 months to the EFF_DATE, and processes the data again until no records remain. 

Let me know if this works or if you have any issues.

 

Luke_C_0-1622738354583.png

 

Luke_C_1-1622738402246.png

 

atcodedog05
18 - Pollux

Wow @Luke_C . You figured it out 😅 i got bit confused when i saw the inital workflow. Kudos to you 🙂👍

 

 

Hi @msnyder 

 

Here is a tutorial video on iterative macro as a refresher https://community.alteryx.com/t5/Videos/Build-Your-First-Iterative-Macro/td-p/55565

 

Hope this helps 🙂

mceleavey
16 - Nebula

Hi @msnyder ,

 

In addition to @Luke_C 's iterative approach, I used a simple batch macro to achieve the same. The importance of this was to ensure it was dynamic.

 

I started by generating the Qtr dates from min to max using the generate rows tool, I then fed this into a batch macro as the control parameter along with the full dataset.

The macro then essentially carries out what you're doing on each branch before unioning and outputting:

 

mceleavey_0-1622739370649.png

 

mceleavey_1-1622739453104.png

 

mceleavey_2-1622739488945.png

 

Hope this helps,

 

M.

 

apathetichell
15 - Aurora

An alternative to  @Luke_C  - here's a batch macro instead of an iterative macro. This version generates period based upon floor division by 3 months and then feeds that into a filter and summarizes.

 

There's an optional text input to change the discount rate for the NPV.

 

FYI - You are receiving cash flows monthly but discounting quarterly which can throw off your numbers.  Attached is a running total NPV analysis with monthly computations. I left in the periodic calculator and the numbers are slightly different because of the monthly vs quarterly discounting.

msnyder
5 - Atom

Thank you all for the great responses to my workflow!

 

@mceleavey 's approach fits my need the best in keeping it simple since I don't need to iterate the whole way through all of the dates.

 

@Luke_C this is a great example of an iterative workflow that I will use as a reference for other projects I work on.

 

@apathetichell I've utilized your NPV input into my workflow as well, great work!

Labels