topic expression with not null values in QlikView

expression with not null values

Thu, 17 May 2012 10:14:21 GMT

Hi all,
I want to calculate the average of the Sales but where the value of the sale is not null!
I try with this expression but nothing
avg({< [Sales] -= {""} >} Sales)

Someone can help me?

Thanks all.

Re: expression with not null values

IAMDV — Thu, 17 May 2012 10:17:47 GMT

How about this expression ?

avg({< [Sales] -= {"=Null()"} >} Sales)

Cheers - DV

expression with not null values

Thu, 17 May 2012 10:22:38 GMT

It doesn't work

Re: expression with not null values

Anonymous — Thu, 17 May 2012 10:24:54 GMT

I think that avg eliminates nulls automatically, no need to do anything about it. That is, just use

avg(Sales)

expression with not null values

Thu, 17 May 2012 10:31:54 GMT

No, avg doesn't eliminate null values!
Now I have seen that when the sale value is null it is rappresented in the table as -
I try: avg({<[Sales]-={'-'}>} Sales) but nothing again.

Re: expression with not null values

IAMDV — Thu, 17 May 2012 10:39:24 GMT

Even, I have assumed that Avg() excludes the Null values. Please can you post the QVW file? It will be easy to see the data. Meanwhile have you tried something like this...

IF(Sales <> Null(), Avg(Sales))

Regards,

expression with not null values

Thu, 17 May 2012 10:53:44 GMT

Hi,

Avg will always exclude the null and non numeric value. So there is no need to do anything. Use AVG(Sales)

Re: expression with not null values

Anonymous — Thu, 17 May 2012 13:20:03 GMT

Hello,

Please, look into attached example. It should give you necessary knowledge to sort out your issue.

Kind regards,

Janusz

Re: expression with not null values

IAMDV — Thu, 17 May 2012 15:00:42 GMT

Janusz - Nice little example! Thanks for sharing.

Cheers - DV

expression with not null values

Fri, 18 May 2012 07:09:10 GMT

Thanks all!