Dynamic Column Calculation

[Verakso]

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

[cmcclellan]

I'm interested if there's a better way, but that's the way I would do it :) 

[GiuseppeC · Accepted solution]

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 solution]

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 solution]

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

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

[Verakso]

First of all a big shout out to @GiuseppeC, @CharlieS, @jrgo and @danilang !

Since I could not prefer one solution over another, I have marked them all!

It really shows the diversity of how you can do things, and also the strength of this community.

I love you all, and thanks for the input

//Verakso