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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
17 - Castor

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
22 - Nova
22 - Nova

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
17 - Castor
17 - Castor

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.

 



Bulien

apathetichell
18 - Pollux

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