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

Multiple calculations using Dynamic Formula macro

sonseeahray
8 - Asteroid

I am attempting to find a solution to perform calculations based on 2 tables. It's difficult for me to explain, so I have screenshots to show what I am trying to accomplish. The final base table will contain 1,000s of combinations to perform calculations from.

 

Base table

sonseeahray_0-1581959310142.png

 

 

Lookup table

sonseeahray_1-1581965095174.png

 

 

 

The field I called "Add values" contains the values that need to be added together from the values inthe lookup table. The screenshot is a very small sample. The lookup table will contain about 100 lookup columns.

 

So, for the V-A combo 20069-130115, the "Add values" field would be the sum of the lookup values for those fields, which would result in this calculation

sonseeahray_0-1581966646530.png

=309,619.139

 

I am able to get a single calculation using the Dynamic Formula macro, as in the screenshot below. 

sonseeahray_0-1581968162828.pngsonseeahray_1-1581968205009.png

How would I modify the Dynamic Formula Macro accomplish getting multiple calculations for one V-A?

 
 

This is a sample of what a list of calculations for one V-A combination would be:

sonseeahray_0-1581971333701.png

 

 

10 REPLIES 10
jyurkovich
7 - Meteor

you don't actually have to use the dynamic formula macro, as you can sum the values easily enough if you restructure (and filter) the data.

 

conveniently, one way to combine all the column names is also how you can sum the columns. the Summary tool has columns with both.

fmvizcaino
17 - Castor
17 - Castor

Hi @sonseeahray ,

 

I'm attaching an example showing how to do it with a batch macro.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

 

The idea is that for every different formula in your data, the macro will run once replacing the number 123456 for the specific formula needed. 

I'm sending the files separately, please leave both in the same folder.

 

Best,

Fernando Vizcaino

sonseeahray
8 - Asteroid

@jyurkovich Thank you. This is useful information.

sonseeahray
8 - Asteroid

@fmvizcaino Hi Fernando, I'm taking a look at your batch macro now.

sonseeahray
8 - Asteroid

@fmvizcaino 😃 I'm sorry... I'm lost...

jyurkovich
7 - Meteor

This version uses the dynamic formula macro to utilize the output of the summarize. You don't need to complicate things with a batch macro.

sonseeahray
8 - Asteroid

@jyurkovich The Summary tool results in only one formula for each V-A combination, where I actually need (in this example) 10 summations for the one V-A combination. In other words, that is 10 results for the one V-A combination rather than one result.

danilang
19 - Altair
19 - Altair

Hi @sonseeahray 

 

Here's a solution using the CReW Dynamic Formula macro.  You'll have to install the macro pack from the link.  If you can't use the macros, because of corporate policy, let us know and we find another solution.

 

MainMain 

The main part provides inputs for the formulas and data

FormulaFormula

 

DataData

 

The Unique and Select on the top branch extract a unique list of V-A Combos from the formulas

 

Macro.png

The batch macro uses two filters to narrow down the formulas and data to a single V-A Combo per iteration of the macro.  these are passed to the Dynamic Formula Macro and the results are unioned together, giving you

 

r.png

 

The results for each formula appear at the end of the data under the formula name column.

 

Dan

 

sonseeahray
8 - Asteroid

@danilang Hi Dan! Thank you very much! I'll see if I have any issues installing the macro pack.

Labels