Mean Range

[#1]

I am trying to develop an I-MR control chart.  Part of that involves calculating the mean range from a moving range.  I was able to create a straight chart with the moving range (I do have null values in my data).  However, I am not able to calculate the mean of the ranges.  The mean should be 41.33, but QlikView just shows me a blank column.  I will also at some point want to turn this into a line chart and am unsure how to do that. Also, I would like the straight table not to show the Candidate, but each time I do that I get a blank chart.

I appreciate any help on this.  I've attached what I have completed so far.

Thanks!

[sunny_talwar]

This may be:

[sunny_talwar]

Alternatively try this expression:

=Avg(Aggr(Fabs(interval(only({<Certified?={'Y'}>}[Certification Date])-only({<Certified?={'Y'}>}[Training Date]),'d')-

Above(total interval(only({<Certified?={'Y'}>}[Certification Date])-only({<Certified?={'Y'}>}[Training Date]),'d'))), Wave, Candidate))

[#4]

Thanks!  What if I want to have the mean range have its own column just like my column 'Mean' does?

[sunny_talwar · Accepted solution]

Try this:

=If([Days to Certify] > 0, Avg(TOTAL Aggr(Fabs(interval(only({<Certified?={'Y'}>}[Certification Date])-only({<Certified?={'Y'}>}[Training Date]),'d')-

Above(total interval(only({<Certified?={'Y'}>}[Certification Date])-only({<Certified?={'Y'}>}[Training Date]),'d'))), Wave, Candidate)))

[#6]

That worked perfectly.  One more question.  Can I make a line chart directly from this straight table, or do I have to reenter the information in the expressions of the line chart?  I will be using a formula similar to this (data mean) + 3*(mean range)/1.128

[sunny_talwar]

I think you should be able to use this straight table (or a copy of this straight table) and move forward from there.