Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
dukane24
Contributor III
Contributor III

Add column values using calculated column names

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!

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

8 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

rubenmarin

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

[$(=FieldNameExpression)]

kdmarkee
Specialist
Specialist

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
MVP & Luminary
MVP & Luminary

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
Specialist
Specialist

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
MVP & Luminary
MVP & Luminary

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
Specialist
Specialist

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.