topic Re: PLEASE help.... in QlikView

PLEASE help....

Fri, 05 Oct 2012 18:25:07 GMT

How do I format an expression to say:

Give the average of the numbers in a field if conditions X, Y, and Z are met.

A	B	C
2011	2012	0
2010	-	1
2011	2011	2

I want to say give me the sum of column C ONLY for the individuals that meet the statement if A > 2010, if B <2012, and C is not (<>) 0.

the 1st colum fails because C is 0

the 2nd column fails because A is not > 2010

The third colum passes since A is > 2010, B is <2012 and C is something other than 0 so I should get a returned value of 2

Is there a way to write an expression that gives you the value for a column for only hte rows that meet the conditions?

Re: PLEASE help....

Fri, 05 Oct 2012 18:27:58 GMT

Use 'and'. For example:

if(A>2010 and B<2012 and C<>0,avg(C))

Re: PLEASE help....

Fri, 05 Oct 2012 18:49:43 GMT

That's what I tried - doesn't work

Re: PLEASE help....

Fri, 05 Oct 2012 18:50:43 GMT

It's probably your formatting then. Do you have a sample file?

Re: PLEASE help....

Fri, 05 Oct 2012 18:57:58 GMT

Unfor. I don't. The script that you put (that I had used before) is saying, if those criteria is selected, then return the value. I want to say just return the value for these conditions - this makes me think that the avg() has to be at the beginning of hte expression - not the end.

So something like

Avg(C), if (A>2010, B<2011, C<>))

Re: PLEASE help....

MayilVahanan — Fri, 05 Oct 2012 21:42:54 GMT

i think your A and B value are string not number, may be. Try like this

=If(A > '2000' and B < '2012' and C > 0, Sum(C)/Sum(total C))

Test like this

=Max(A) gives any value or use MaxString(A) gives value. From this, you can get idea..

Hope it helps