topic Re: Average in script in QlikView

Average in script

samuel_brierley — Fri, 01 Aug 2014 07:58:07 GMT

Hi all first of all thanks for any help,

I basically have 4 speed measurements that i would like to average in the script however if no speed is measured it is recorded as an X.

how therefore do i only average those that are numbers?

I have tried below but this doesnt like the X's i presume,

[Speed(Lane 1)]+[Speed(Lane 2)]+[Speed(Lane 3)]+[Speed(Lane 4)])/4) as [Average Speed]

MK_QSL — Fri, 01 Aug 2014 08:01:46 GMT

Provide data and your required output... please..

jonathandienst — Fri, 01 Aug 2014 08:02:14 GMT

=RangeAvg([Speed(Lane 1)], [Speed(Lane 2)], [Speed(Lane 3)], [Speed(Lane 4)]) as [Average Speed]

HTH

Jonathan

samuel_brierley — Fri, 01 Aug 2014 08:02:54 GMT

HI thanks for the input however I cant treat them as zeros because that will lower my average.

simenkg — Fri, 01 Aug 2014 08:06:59 GMT

rangesum([Speed(Lane 1)]+[Speed(Lane 2)]+[Speed(Lane 3)]+[Speed(Lane 4)])/

(4 - (if([Speed(Lane 1)]='X',1,0) + if([Speed(Lane 2)]='X',1,0) + if([Speed(Lane 3)]='X',1,0) + if([Speed(Lane 4)]='X',1,0))) as [Average Speed]

samuel_brierley — Fri, 01 Aug 2014 08:08:38 GMT

thats the logic thanks SImen

jonathandienst — Fri, 01 Aug 2014 08:10:18 GMT

=RangeSum([Speed(Lane 1)], [Speed(Lane 2)], [Speed(Lane 3)], [Speed(Lane 4)]) /

RangeCount([Speed(Lane 1)], [Speed(Lane 2)], [Speed(Lane 3)], [Speed(Lane 4)]) as [Average Speed]

simenkg — Fri, 01 Aug 2014 08:12:14 GMT

I actually think this one is better:

rangesum([Speed(Lane 1)]+[Speed(Lane 2)]+[Speed(Lane 3)]+[Speed(Lane 4)]/

rangenumericcount([Speed(Lane 1)],[Speed(Lane 2)],[Speed(Lane 3)],[Speed(Lane 4)])

as [Average Speed]

Fri, 01 Aug 2014 08:14:12 GMT

RangeAvg( if(isnum([Speed(Lane 1)]),[Speed(Lane 1)]),

if(isnum([Speed(Lane 2)]),[Speed(Lane 2)]),

if(isnum([Speed(Lane 3)]),[Speed(Lane 3)]),

if(isnum([Speed(Lane 4)]),[Speed(Lane 4)])) as [Average Speed]

simenkg — Fri, 01 Aug 2014 08:16:18 GMT

Be sure to mark the correct answer as such so that others don't have to read through the whole post to get the right answer.