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

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

Fri, 13 Jul 2012 10:18:14 GMT

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.

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

swuehl — Fri, 13 Jul 2012 10:23:14 GMT

Try maybe

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

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

Fri, 13 Jul 2012 10:31:49 GMT

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

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

Fri, 13 Jul 2012 11:10:15 GMT

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.

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

Fri, 13 Jul 2012 11:22:08 GMT

what about...

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

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

Fri, 13 Jul 2012 11:22:18 GMT

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

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

Fri, 13 Jul 2012 13:52:24 GMT

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.

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

Fri, 13 Jul 2012 13:52:31 GMT

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.

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

swuehl — Fri, 13 Jul 2012 14:17:15 GMT

Not 100% sure what you are after.

Maybe like attached?

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

Fri, 13 Jul 2012 16:09:44 GMT

Nulls are excluded in averaging anyway?