topic Re: Averageif(s) in QlikView

Averageif(s)

robert_mika — Thu, 22 May 2014 15:26:34 GMT

I know how to Average with one condition but is it possible to add 2 or more?

Re: Averageif(s)

maxgro — Thu, 22 May 2014 15:38:42 GMT

maybe this?

=Avg(if(match(Product,'a','b','c') and ProductType <>'B' or ProductType like '*C*', Expression1))

Re: Averageif(s)

swuehl — Thu, 22 May 2014 15:40:12 GMT

Not sure if I understood correctly, maybe something like this?

=avg(if( (FIELD1 >= 10 and FIELD2 = 'A') or FIELD3 <= 10, VALUE))

Re: Averageif(s)

jvitantonio — Thu, 22 May 2014 16:03:22 GMT

Hi Robert,

I would avoid using IF's and use Set Analysis instead.

Example:

=Avg({< MyField = {">=10<250"} >} MyField)

The above would be the same as Avg(If(MyField >=10 and MyField <250, MyField)) but would perform better.

Best

Re: Averageif(s)

robert_mika — Thu, 22 May 2014 20:36:48 GMT

Thank you all for answer but I may need something else

Let say I have table

Cost

Now I need average

<=10

and

>10 and <21

so the answers will be

I do not want to hard coded any of the numbers

I would like to point to another column or object

let say

Low High Result

0 10 10

10 20 20

Re: Averageif(s)

swuehl — Thu, 22 May 2014 21:13:53 GMT

There are several possible solutions. For example, create a calculated dimension to create your buckets like

=class(FIELD, 10)

and an expression like

=avg(FIELD)

Or link your FIELD to a table with intervals using Intervalmatch Load prefix.

Re: Averageif(s)

robert_mika — Fri, 23 May 2014 04:09:56 GMT

swuehl

This is it

Thank you.

Is it possible to add 3 criteria:

Let say

Between

10 and 20 and when another field is let say A?

Re: Averageif(s)

swuehl — Fri, 23 May 2014 09:39:57 GMT

I think you can add that condition in the avg() function:

=avg(if(AnotherField = 'A', FIELD))

or using set analysis:

=avg({<AnotherField = {'A'}>} FIELD)

Re: Averageif(s)

robert_mika — Fri, 23 May 2014 09:43:26 GMT

Thnak you but is this posiible to use another column instead of hard coded 'A'.

I assume that this may need to add another calculated dimension?

Or am I worng?