[SHARING] How to: Dynamic Formula Example with new Fields

[caltang]

Hi there! 

I am writing this community post to share how one can do dynamic formulas with new fields. 

Imagine a scenario where you get data every month and the data comes in new fields (columns) each month. You may want to pivot and summarize them as a dynamic measure, but there are some scenarios whereby a different approach may be needed for a myriad of reasons.

For instance, in actuarial reporting, sometimes new fields need to be included in their analysis per requirement changes. This may mean completely different fields get added, and formulas need to be made dynamic to account for new or dropped fields. If the data set is huge, and if the analyst is working on a VDI machine... then pivoting columns into rows and then back after the calculation may slow the process down dramatically. 

So how can we have a dynamic formula that takes in new fields or whatever fields we want? We use a batch macro process to achieve this:

The workflow is attached as an example. 4 inputs represents 4 months of changes... with the final output just showing the result.

So the concept is like this:

You can see, four inputs representing the first month, second month, third month, and fourth month of data. A new field is added... so we have to make the "Total" dynamic... this is how:

The macro operates on the basis of just changing the formula in the formula tool via the action tool.

This coupled with the settings on the batch macro via the interface designer:

This basically allows the macro to read your desired formula and it changes the expression in the formula tool to get the total you want dynamically. Of course, we need to prepare the data to get to this change as well, which explains this step in the main workflow:

The idea here is to basically get all the fields we want via the Field Info tool, then make sure to change the data type to V_WString with a longer field length, and to add "[" and "]" at the start and end of each field to be aligned to the expression builder. Then the Summarize tool basically concatenates the fields we want with the formula "+" as the separator, thereby making it a formula in an output like so:

Of course, this is a SIMPLE use case. You can add filters and make it conditional or even more dynamic to get what you want. Ultimately, this example sharing is to showcase end users how they can achieve something dynamic for formulas and fields via a batch macro and with a simple slate of tools.

The end result if compared between four periods looks like this:

There you have it! I hope this was informative and useful to community members. Let me know if you like posts like this and would like other tips - perhaps on XML hacking next?

Happy to share!