Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

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

Try maybe

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

Not applicable

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

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

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

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

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

what about...

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

?

Not applicable

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

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

Not applicable

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

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

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

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.

MVP
MVP

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

Not 100% sure what you are after.

Maybe like attached?

Not applicable

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

Nulls are excluded in averaging anyway?

Community Browser