Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dominicelfick
Not applicable

Removing illogical numbers from an average

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

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Removing illogical numbers from an average

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

2 Replies
swuehl
Not applicable

Re: Removing illogical numbers from an average

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

dominicelfick
Not applicable

Re: Removing illogical numbers from an average

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