Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

9 Replies
swuehl
MVP
MVP

Try maybe

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

what about...

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

?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not 100% sure what you are after.

Maybe like attached?

Not applicable
Author

Nulls are excluded in averaging anyway?