topic Re: simple expressions in QlikView

simple expressions

Thu, 30 Aug 2012 00:08:30 GMT

I have a pivot table with three expressions. I would like to sum together the result of these three expressions into a new 4th expression. Is there a way to pull the results from the first three into the new expression, or do i just have to replicate the first three computations inside the new expression?

for example:

A+B=C

X-Y=D

K(5)/X=M

I would like to learn how to write an expression C+D+M.

Or is there a better way to get it into the pivot table other than an expression? Be gentle I am just an accountant. I see some of the questions some of you post and it looks like hieroglyphs.....and very impressive ones at that.

Thank you in advance for any replys.

Re: simple expressions

sebastiandperei — Thu, 30 Aug 2012 00:22:29 GMT

Hi john,

You must put a Label of each expression. Then, in fourth expression, use the names:

Expression1: Sum(field1)

Expression2: Avg(field2)

Expression3: Count(field3)

Expression4: (Expression1/Expression2)+Expression3

Remind to use exactly the same labels.

Re: simple expressions

rajni_batra — Thu, 30 Aug 2012 07:06:48 GMT

use column(1)+column(2)+column(3)

Re: simple expressions

sebastiandperei — Thu, 30 Aug 2012 11:26:04 GMT

Use the names. If you change the expression order, you will must modify the expression4

Re: simple expressions

Thu, 30 Aug 2012 12:39:54 GMT

Hi John,

Follow Sebastian suggestion. Give particular name to each expression and just use that name in the 4th expression like

Exp1+Exp2+EXP3. If you use Column then if you change the order it will reflect in the result of 4th expression.

Re: simple expressions

Thu, 30 Aug 2012 13:36:20 GMT

Still having difficulty (probably missing something very basic)

Expression1 label is Total Value and definition is Sum ([Value])

Expression 2 label is Overhead and definition is Sum ([Overhead])

when I try to add the expression (exactly as it reads here): Total Value + Overhead there is no result. It does not indicate an error or bad field name, just returns a result of "-" (the correct result should be a positive whole number).

I tried a simpler formulae to troubleshoot

if I add the expression sum ('Total Value') to try to see if I could just pull in the result from just the first expression, it returns a zero. By adding quotes to the label it changed the result from a "-" to a zero, but still not the correct positive number.

Re: simple expressions

Anonymous — Thu, 30 Aug 2012 14:43:00 GMT

use coloumn function

i.e coloumn(1)+coloumn(2)+coloumn(3)

Re: simple expressions

Thu, 30 Aug 2012 15:39:20 GMT

I'm sorry I am not able to get that to work either.....just returns "-"

I feel like I am getting the syntax wrong by omitting a [ or ' or some other little thing

Re: simple expressions

Thu, 30 Aug 2012 16:07:19 GMT

Here is an example of what srikanthsri1 suggested. Using the columns expression is easiest - I can never seem to get the titles to work right but maybe that's just me.

Re: simple expressions

Thu, 30 Aug 2012 16:32:12 GMT

Success with the column expression. I would rather use the expression labels so I don't have to change the expression if i move columns......but for now I am tired of messing with it.

Thank you VERY much for sending the test file. That made all the difference.

Re: simple expressions

sebastiandperei — Thu, 30 Aug 2012 18:13:36 GMT

Matt,

In first place, if your expression label has special characters (in this case, the space in Total Value), you must use []

For example: [Total Value]+[Overhead]

In second place, you can't use Sum([Total Value]), because [Total Values] is only a value, the result of the expression in this cell of the table. You only can use the label (or column()) in arithmetic operations, or with OTHER expression.

For Example: [Total Value] + Avg(Tax)

Re: simple expressions

Thu, 30 Aug 2012 18:17:24 GMT

Thanks, Sebastian! That worked for me. No wonder I could never get it to work before. Here you go, Matt.

Re: simple expressions

Thu, 30 Aug 2012 18:40:26 GMT

Ditto, that worked. Thank you all for being so helpful (and patient).

Matt