topic Re: incorrect Moving Avg and Upper control in QlikView

incorrect Moving Avg and Upper control

happyjoshua777 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have followed all the steps which in 'Creating a Moving Range chart ' of the Cookbook, namely:

LOAD @1:7 As Year,

@8:11 As Month,

@12:18 as [MaxTemp(C)],

@19:26 As [MinTemp(C)],

@27:34 as [AF Days],

@35:42 as [Rain(mm)],

@43:50 as [Sun Hours]

FROM

[http://www.metoffice.gov.uk/pub/data/weather/uk/climate/stationdata/heathrowdata.txt]

(fix, codepage is 1252, header is 7 lines)

Where @1:7 < 2013;

* Created a new line chart

* The added expressions:

Rainfall(mm)

Sum([Rain(mm)])

Moving Range	fabs(Above(Sum([Rain(mm)]))-Sum([Rain(mm)]))
Moving Avg	Avg({<Year={">=1961<=1990"}>} total Aggr(fabs(Ab ove(Sum({<Year={">=1961<=1990"}>} [Rain(mm)]))- Sum({<Year={">=1961<=1990"}>} [Rain(mm)])), Year))
Upper Control	[Moving Avg] * 3.267

and I got the chart as in moving_avg.png

As one can see the Moving Avg and Upper Control are way below the thresholds

However, the results should be as per Moving_Range_correct.png

as described on p. 130 of the Cookbook

how can I correct the output I am currently receiving from the Control Chart (see moving_avg.png)?

thanks

Re: incorrect Moving Avg and Upper control

techvarun — Mon, 07 Aug 2017 07:29:56 GMT

Are you looking for something like the attached?

Re: incorrect Moving Avg and Upper control

effinty2112 — Mon, 07 Aug 2017 13:22:46 GMT

Hello,

Take out the Rainfall(mm) expression - it doesn't belong in this chart that's looking at variation rather than value. The Moving Range expression does belong but I can't see it in your image. Check it's there and if so that it hasn't been hasn't been disabled.

Cheers

Andrew

Re: incorrect Moving Avg and Upper control

happyjoshua777 — Wed, 09 Aug 2017 04:22:15 GMT

Below is the screen shot from the Cookbook.

as one can see Rainfall(mm) expression does very much belong there.

I went through the instruction again and got the correct results refer to control_chart_moving_rng.png

I believe the issue was due to [Avg] expression being mislabeled as something like 'Moving Avg'

Which caused Upper and Lower Process Limit expressions not be able to refer to it as, for example, Upper ... Limit expressions is as follows:

[Avg] + (Avg({<Year={">=1960<=1980"}>} total Aggr(fabs(Above(Sum({<Year={">=1960<=1980"}>}[Rain(mm)]))-Sum({<Year={">=1960<=1980"}>}[Rain(mm)])), Year)) * 2.66)

As one can see it utilises [Avg] and not something like [Moving Avg]

thanks

Re: incorrect Moving Avg and Upper control

happyjoshua777 — Wed, 09 Aug 2017 04:23:17 GMT

was not able to open this qvw file as I have a free version of of Qv

please copy and paste your code

Re: incorrect Moving Avg and Upper control

Anil_Babu_Samineni — Wed, 09 Aug 2017 04:28:11 GMT

Script here

LOAD @1:7 As Year,

@8:11 As Month,

@12:18 as [MaxTemp(C)],

@19:26 As [MinTemp(C)],

@27:34 as [AF Days],

@35:42 as [Rain(mm)],

@43:50 as [Sun Hours]

FROM

[http://www.metoffice.gov.uk/pub/data/weather/uk/climate/stationdata/heathrowdata.txt]

(fix, codepage is 1252, header is 7 lines)

Where @1:7 < 2013;

Front end

Year is dimension

Expressions are

1) fabs(Above(Sum([Rain(mm)]))-Sum([Rain(mm)]))

2) Avg({<Year={">=1961<=1990"}>} total Aggr(fabs(Above(Sum({<Year={">=1961<=1990"}>} [Rain(mm)]))-

Sum({<Year={">=1961<=1990"}>} [Rain(mm)])), Year))

3) Column(2) * 3.267