Dynamic Column Calculation

Question

Hi

I was wondering if there was a clever way to accomplish this:

I have some data like this:

The idea is to ad some sums for some of the columns, like above

Here I have just used the Formula tool, to accomplish this

All though this works, I can't quite shake the feeling that this could be done i clever way, am I right?

//Verakso

Question.yxmd

GiuseppeC · Accepted Answer

Hi @Verakso,

see attached. Not necessarily a 'better' way, but it's another way!

This is helpful when you have a long list of fields you need to sum up by row and don't want to manually add the fields one by one. Also, it can make your logic dynamic in some circumstances, for example when you want a new field in a new data set that you input to be added automatically without changing formula in your workflow. This can be achieved by selecting the *Unknown field within the Transpose/Crosstab tools.

Hope this helps!

Giuseppe

CharlieS · Accepted Answer

This is possible via XML editing a batch macro. Check out the attached solution to see how it could work.I just used groups of three columns, but adjusting that logic should be simple enough.

@Claje showed off the power of nested macro and XML editing in his impressive Inspire session. Maybe he'll share the presentation to keep that inspiration going.

jrgo · Accepted Answer

@Verakso ,

Here's another option, however, not sure how applicable this will be to whatever you need to apply to since your example suggests to sum 3 adjacent fields except for column D, but no logic provided to why that column was excluded so that's the only part of this that is not dynamic.

Hope this helps!

Jimmy
Teknion Data Solutions

danilang · Accepted Answer

Hi @Verakso et al.

Here's yet another way.

The Sum Groupings holds a list of the groups of sums that you want to produce in 2 columns SumGroup and Column

This is joined to the transposed data table and the results are summed grouped by SumGroup and RecordID and cross tabbed by RecordID.  The dynamic rename gives you sensible column names that are joined back to the original data giving

Change the Sum Groupings table and change the result.  The columns can even appear in multiple groups. Below I added a 4th group with all the columns (excl D) to give a grand total

Dan