Dynamically Re-adding in Formula Tool for New Granularity (Like Calc Field in Pivot Table)

Hello wonderful Alteryx Community,

I built a macro that dynamically summarizes certain fields and will sum up the metrics for whichever dimensions we choose, but I want to add in a step that will automatically recalculate all the formulas at the new level of granularity (basically like a calculated field in a pivot table in Excel). I have the formulas in the tool before inputting into the macro, and as you see within the macro, I’ve used a sequence of Field Info, Formula an Summarize tool to write the formulas as if they were XML code. I’m looking to insert a tool before the Macro Output that will dynamically re-add in all of these formulas (and of course, in a way that will automatically update if I choose to add in more or less formulas in the parent workflow) so that our summarized Output data will automatically have these formulas calculated.

The constraints are that this has to either be done within the macro (or within another macro within that macro, but I’d prefer to it all be within a single macro).

I’ve attached a workflow of completely nonsensical dimensions, metrics and formulas, but the logic is all there.

Thanks so much.

Test Summarize.yxzp

Datasets

Reporting

Accepted answers

NicoleJohnson

Fun one! I recently discovered the Dynamic Formula tool in the CReW Macros toolset (http://www.chaosreignswithin.com/p/macros.html) and this is an excellent use case for it. The tool basically needs 4 fields - Output Field (the name of your formula), Size, Type, and Expression. With a simplification of the formula you were generating based on results of the Field Info tool, you can easily create all 4 of these fields and run them through the Dynamic Formula tool to create your desired calculations.

See attached workflow example! Hope this helps! 🙂

Cheers,

Test Summarize with Dynamic Formula.yxzp

All comments

danilang

hi @KieranRidge

You're using a RightPart() custom function in your macro. Can you share either the custom formula addin or a description of what it does?

Dan

NicoleJohnson

See attached workflow example! Hope this helps! 🙂

Cheers,

Test Summarize with Dynamic Formula.yxzp

KieranRidge

Hi Nicole,

Thanks so much – it definitely helps. It looks like that macro was a few steps deeper than I needed so I simplified it into one secondary batch macro where the batch input is the XML code and the regular input is the primary macro.I also changed the secondary batch macro to only update the FormulaField code section of the Formula Tool as opposed to the entire thing.

I do have a question about the primary macro (Test Summarize) though. As you see, it now has the secondary macro with the batch input – would we be able to do this process within the primary macro, leaving out the secondary entirely? This would involve a new control parameter with the XML text output, right? But since we didn’t calculate that until within the macro, does that mean we have absolutely have to create an additional macro for the data to dynamically update? Clearly, I’m still trying to reach my goal of having our parent workflow only contain our initial formulas whereas all of our macro work would be completed in a singular macro . Please let me know if you have any thoughts – reattaching my updated workflow.

PS Dan – the Field Info, Formula and Summarize combo would change take our initial formulas from the primary Formula tool and convert them to be displayed as <FormulaField expression="[Metric D]/[Metric B]" field="Calc1" size="8" type="Double" /><FormulaField expression="([Metric C]-[Metric B])/[metric B]" field="Calc2" size="8" type="Double" /><FormulaField expression="([Metric A]*[Metric A])/([Metric B]-[M... etc. – which is something both Nicole’s and my workflow had.

Test Summarizev2.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703