Using Expressions withing Expressions

[#1]

Hi,

Is it possible to use an expression within an expression? If so can I simply use the expression name?

For example

I want to use the total of an expression in a calculation. For example column A,B and C:

C = A*B / [Total Column B]

Column B is another expression.

Sorry if the answer is an obvious one ...

[#2]

Very well... For eg, in chart if you have one expression with label 'Net Book Value' , can refer the same in the second expression just by mentioning the label/ID (eg. [Net Book Value' ]/Cost). No need ot repeat the forst expression again.

--Arun

[#3 · Accepted solution]

No problem with "obvious" stuff, easy to answer but only obvious when you know it.

You can do this two ways:

Method 1 - Column numbers

Each expression has a number assigned to it, which equates to 1 for the first, 2 for the second and so on. In your expression you can say =Column(1) * Column(2) / Column(3). However, in order to achieve your requirement for TOTAL you probably need to set up another column which is hidden and represents the total value, then refer to this column.

Method 2 - Names

Each expression has a label associated with it, so for example if your columns A & B are called Sales & Cost then you can write an expression such as =[Sales] * [Cost], again you will need an additional hidden column for total cost in which case your expression would become =[Sales] * [Cost] / [Total Cost]

I prefer method 2 simply because it is more scalable and prevents problems when moving columns around.

Good luck,

[#4]

Hi thanks for the descriptive reply.

I've tried method two but for some reason the expression builder does not recognise the the previous expression label?

[#5]

Try to copy the label from the properties and paste (within [ ] if space)

--Arun

[#6]

This appears to be an issue trying to use an aggregate on the Expression.

To continue with Nigel's example:

=[Sales] * [Cost] - this works fine but =sum([Cost]) will not recognise the expression [Cost]

Any thoughts?

[#7]

Hi Grinja

You cannot do Sum([Cost]) because this will not work at all, the idea of using the column name or number is that QlikView will give you the absolute value of what is in that cell, it does not allow you to execute sums/maximums/minimums or anything else, so you have to be a bit clever about getting around this.

As I suggested earlier, if you want to use the Sum of Cost in your expression then you must:

1. Create a new column which contains the sum value you want to use, call this column SumCost or similar and HIDE the column so it doesn't appear on your chart/table.

2. In your expression that uses the column names, you should now amend this to read: =( [Sales] * [Cost] ) / [SumCost]

Regards,

[#8]

Thanks for the explanation and walkthrough.

I also found that I can assign a variable as the Total Cost. From a performance perspective is it better to include a hidden column or assign the variable ... I currently notice no difference.