topic Re: Removing illogical numbers from an average in QlikView

Removing illogical numbers from an average

Anonymous — Sat, 25 Aug 2012 17:12:47 GMT

I am trying to calculate an average service score for deliveries. The scores are Bad, poor, fair and excellent and have been coverted into 25, 50, 75 or 100 unfortunately in teh conversion process unreported values (of which there are a lot) have been converted to -1 and N/A values have been converted to 0. This is making it very difficult to calculate a correct average.

Without having to go back to the programmers and getting them to change something on the load is there a way to exclude these "incorrect" values and get a proper average? I have tried turning them into nulls, but then I get no average at all,

and I have turning them into 1's but that also throws a weird average

if([Quality Rating Int]>24,sum ([Quality Rating Int]),1) / if([Quality Rating Int]>24,NumericCount ([Quality Rating Int]),1)

I have tried using the rangeavg but that throws "funnys" too

rangeAvg ([Service Rating Int])

I am sure there is a really simple way to do this but I can not find it. Any help is greatly appreciated.

Thanks

Re: Removing illogical numbers from an average

swuehl — Sat, 25 Aug 2012 18:04:46 GMT

I think you need to embed the conditional into your aggregation (sum) function, not the other way round.

Try something like

=avg( if([Quality Rating Int]>24, [Quality Rating Int]))

You can also use a so called set expression to filter your Ratings:

=avg( {<[Quality Rating Int] = {">24"}>} [Quality Rating Int])

Hope this helps,

Stefan

Re: Removing illogical numbers from an average

Anonymous — Sun, 26 Aug 2012 09:43:16 GMT

Brilliant, thanks so much for you help. Worked first time perfectly.