Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

PLEASE help....

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.

ABC
201120120
2010-1
201120112

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?

5 Replies
Not applicable

Re: PLEASE help....

Use 'and'. For example:

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

Not applicable

Re: PLEASE help....

That's what I tried - doesn't work

Not applicable

Re: PLEASE help....

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

Not applicable

Re: PLEASE help....

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

Hi

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