Multiple calculations using Dynamic Formula macro

[sonseeahray]

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

Lookup table

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

=309,619.139

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

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:

[jyurkovich]

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]

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]

@jyurkovich Thank you. This is useful information.

[sonseeahray]

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

[sonseeahray]

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

[jyurkovich]

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]

@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 · Accepted solution]

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.

Main 

The main part provides inputs for the formulas and data

Formula

Data

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

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

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

Dan

[sonseeahray]

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