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

[KieranRidge]

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.

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

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,

NJ

[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.

[danilang]

Hi @KieranRidge 

I was able to figure out what you intended to do with your macro. 

My question was directly related to Formula Tool(11) in your original Macro.  Its formula is this

if Contains([Source],"Formula") then

'<FormulaField expression="'+RIGHTPART([source], ": ")+'" field="'+[Name]+'" size="8" type="Double" />'
else ""
endif

RIGHTPART() is not a built-in Alteryx formula.  Can you share this formula add-in?  It probably does something like Substring([source],findstring([source],": ")) but it would be nice to know for sure.

Thanks 

Dan 

[KieranRidge]

Thanks for clarifying, Dan, and I am happy to answer. If [field]= "separate+this" then RIGHTPART([field], "+") will yield "this". I am unsure what package to download to incorporate.

[danilang]

Thanks @KieranRidge 

After some hunting I was able to track it down to an Alteryx formula addin created by the illustrious @jdunkerley79.    You can find more info here

Dan