Averages - excluding 'blank' or 'N/A' entries

[#1]

Hello,

I am attempting to generate Average expressions, but I am currently experiencing difficulty in excluding entries where '0's, 'blanks' or 'N/A's appear.

Is there a way in which this can be embedded within the standard Average expression?  If so, how do I go about achieving this please?

Thanks and regards, Mike.

[swuehl]

Try maybe

avg({<Value = {">0"}>} Value)

[#3]

You could use something like the following for your dimension:   =if(Sales >0 and Sales <>'' and Sales <> 'N/A',Month)  Where   1. Sales is the filed you want to average  2. Month is your dimension    Regards  Rahul

[#4]

Hi 'swuehl' and Rahul,

I have tried your suggestions but neither appear to work within the parameters of my particular problem. I'm not quite sure why they do not appear to be working as on paper they should work.

Please find attached the Qlikview application where I am attempting to apply the condition to exclude the zeros.

You will see alongside the matrix 3 list boxes for each category.  If I select the value excluding the zeros it corrects the averages, however if apply the filter to one category it also affects the other two categories in that it omits data some of which might have values which I would want to be able to incorporate into the finished matrix.

Any help / advice would be greatly appreciated.

Thanks and regards, Mike.

[#5]

what about...

sum([Furniture Quantities])    /     count({$<[Furniture Quantities]={'>0'}>}[Furniture Quantities])

?

[#6]

Your solution use of avg(if) something like:   avg(if([Furniture Quantities]>0,[Furniture Quantities]))  Regards  Rahul

[#7]

Sorry people,

Your suggestions doesn't appear to work within the matrix.  If you try and apply your expression to the table attached above (Averages.qvw) you will see that it doesn't work.

Onwards and upwards!

Thanks and regards, Mike.

[#8]

Sorry people,

Your suggestions doesn't appear to work within the matrix.  If you try and apply your expression to the table attached above (Averages.qvw) you will see that it doesn't work.

Onwards and upwards!

Thanks and regards, Mike.

[swuehl]

Not 100% sure what you are after.

Maybe like attached?

[#10]

Nulls are excluded in averaging anyway?