Add column values using calculated column names

[dukane24]

Hello, I have a need to add some custom subtotal columns to a large pivot table.  I know you can add column values together referencing the column names like this:

"Column Name 1" + "Column Name 2"

Easy peasy.  But the problem I have is that I needed to use calculated column names to get the formatting that I want.  It's nothing too complicated, but I added chr(10) in some places so I could specify where it puts the carriage return, like this:

'Column' & chr(10) & 'Name 1' so that it comes out like this:

Column

Name 1

That's just an example.  But is there a way I can add columns together, referencing the column name, if the column name is calculated like this?

Thanks!

[Anil_Babu_Samineni]

The statement looks good, What alternative you are going to have?

[marcus_sommer · Accepted solution]

You could try something this:

$(='"Column' & chr(10) & 'Name 1"')

If the positions of the columns are static you could also use the index of column like:

column(1)

- Marcus

[rubenmarin]

Hi Nate, you can use the same expression used to name the fields in a dollar expansion:

[$(=FieldNameExpression)]

[kdmarkee]

What if you wanted to do something similar but add all the values of a single column, AND NOT one rows's values for more than 1 column...adding down vs left and right, so to speak.  I have a weird straight table that is built where the starting point is input variables, then some expressions based on that, and then some expressions based on the first set of expressions, etc....a long trail of dependency of expressions in which I am using the expression label names to build beyond the initial expression using the input variables.  See attached.  Thanks.

[marcus_sommer]

On the basis of your example I couldn't see what your problem is - the if-loop to grab the Item or further calculations on them or the labels of these calculations. Please elaborate it more clearly.

- Marcus

[kdmarkee]

My apologies, I mean to attach an updated qvw to better explain my need, so I have done that here.  Hope that helps explain what I need.  Thanks.

[marcus_sommer]

You could use an aggr() to get the totals of your normal expressions like:

sum(aggr(

if(Item='A', $(vMyVariableA),

  if(Item='B', $(vMyVariableB),

    if(Item='C', $(vMyVariableC)

))), Item))

which lead also to a total on the further calculations but probably not those which you want, see:

If they should be different you would probaly again need the aggr but unfortunately didn't work them with the column() index or a label-referencing which meant that you would need to use the origin expressions within the aggr there:

sum(aggr(

if(Item='A', $(vMyVariableA) * $(vMyVariableX),

  if(Item='B', $(vMyVariableB) * $(vMyVariableY),

    if(Item='C', $(vMyVariableC) * $(vMyVariableZ)

))), Item))

In general you will need a conditional-logic to assign dimension and variable but the if-loop could be replaced with a pick(match()) combination and could be also loaded as table-fields and applied per concat within $-sign expansion like in this example: Re: Pick(Match()) using a variable - could be used for the return-list, too and the expression itself could be even within a (maybe parametrized) variable.

This meant in general you should be able to implement what you but it won't be very easy to get all the variable-syntax stuff right and will take some efforts. Here you will find more about Variables.

- Marcus

[kdmarkee]

Thank you for your help, it allowed me to get further with my oddball project.  I was able to use sum(aggr()) for all the expressions, and then use the column label names in later expressions with the Total Mode property of the expression set to Sum of Rows, and that gave me totals within the chart.  I didn't read much yet about Pick(Match()) or variables, yet however.  I do have another issue with regards to this same project that I don't know how to solve but I'll open a new discussion since it will probably be out of scope here.