is it possible to create a ImR chart in Qlikview?

[#1]

Im trying to create a ImR chart in qlikview. the problem is that Im not using the standard deviation to calvulate the control llimits, instead I want to use the average moving range(mRbar).

The moving range in a data set is calculated like this:

Subtract the second data point from the first data point and record this value. As an example take a data set of {1, 4, 4, 2, 7, 3}. Subtracting the second data point from the first gives us: 1-4 = -3

Take the absolute value of the result. Continuing the example: abs(-3) = 3. Record the result as the first entry in a list.

Repeat step 1 and 2 for the rest of the data points starting by subtracting the third from the second. Again from the example data set, {1, 4, 4, 2, 7, 3} : {(1-4), (4-4), (4-2), (2-7), (7-3)} = {-3, 0, 2, -5, 4} = {3, 0, 2, 5, 4}. This list is the moving range for your data set.

I want to use this calculated value as a reference line in a line chart.

Thanks

[johnw · Accepted solution]

Perhaps this?

fabs(above(DataPoint) - DataPoint)

[#3]

Thanks John,

Now I can calculate the moving range, but how do I do if I want the average moving range displayed in the graph?

The formula for the upper control limit in a ImR chart is Xbar+(2,66*mRbar)

I´m pretty new to qlikview and I tried to write avg(fabs(above(Datapoint)-Datapoint), but that didn´t work.

[johnw]

How do you define the average moving range? The average of all the moving range values up to and including that one? So in your example where your moving range for the data set was {3,0,2,5,4}, would it be {3,(3+0)/2,(3+0+2)/3,(3+0+2+5)/4,(3+0+2+5+4)/5} = {3,1.5,1.67,2.5,2.8}?

Hmmm. Had to actually build the example and experiment to figure it out, but it looks like this produces those numbers:

rangeavg(above(fabs(above(DataPoint)-DataPoint),0,rowno()))

Mind you, if you start making selections that restrict the number of data points, this all starts to break down, and we'd need an even more complicated solution, probably involving changes to your data model to store the necessary relationships or even the resulting values. But I'm not sure if that's a practical issue for you.

[#5]

Thanks a lot John!

I used the expression I got from you and calculated the Control Limits with this formula:

avg(TOTAL aggr(sum(DataPoint),Month))-2.66*fabs(above(column(1))-Column(1)) (just replace - with + to get upper control limit)

I check the box to show the avarage and then hide the calculated line itself, this works really good and I have validated the results with MiniTab.

Regards

[#6]

Hello John,

Could you add this formula of 'holgardj' in you example, i'm having problems getting it to work.

avg(TOTAL aggr(sum(DataPoint),Month))-2.66*fabs(above(column(1))-Column(1)) (just replace - with + to get upper control limit)

thanks Mireille