Moving Range calculation for I-MR control chart

P

peter789

Board Regular
Joined
Nov 20, 2016
Messages
130
As the next step of my control chart project I realise I need to calculate the average of the Moving Range of a group of values in my Pivot Table.
Wikipedia defines the calculation formula here:

https://en.wikipedia.org/wiki/Shewhart_individuals_control_chart

As a first step I have created an Index column in my data using Power Query. Next I tried to test the use of EARLIER to find the minimum of one value and its neighbour in the next row. However this is where it gets stuck.
Any help with the measure I am using would be appreciated please or advice as to whether I should be taking a different tack. I have been reluctant to try and create calculated columns in my data source as just even adding the index column has increased the file size by 20%.
The measure I've tried is:

=CALCULATE(MIN(QryAnalysis[Value]),FILTER(QryAnalysis,QryAnalysis[Oxide]=EARLIER(QryAnalysis[Oxide]) && QryAnalysis[Index]=EARLIER((QryAnalysis[Index]) && QryAnalysis[Index]=EARLIER(QryAnalysis[Index])+1)))

The error is:
"This formula is invalid or incomplete: 'Calculation error in measure 'QryAnalysis'[Minimum of two values]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.'. "

My Pivot Table looks like:
[TABLE="width: 288"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Oxide[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Na2O %[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Average Value[/TD]
[/TR]
[TR]
[TD]Sample Date[/TD]
[TD]Index[/TD]
[TD]Amber[/TD]
[/TR]
[TR]
[TD="align: right"]03/04/2017
[/TD]
[TD="align: right"]132950[/TD]
[TD="align: right"]11.97[/TD]
[/TR]
[TR]
[TD="align: right"]10/04/2017
[/TD]
[TD="align: right"]132951[/TD]
[TD="align: right"]12.00[/TD]
[/TR]
[TR]
[TD="align: right"]18/04/2017[/TD]
[TD="align: right"]132952[/TD]
[TD="align: right"]11.96[/TD]
[/TR]
[TR]
[TD="align: right"]24/04/2017[/TD]
[TD="align: right"]132953[/TD]
[TD="align: right"]12.09[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD="align: right"]132954[/TD]
[TD="align: right"]12.11[/TD]
[/TR]
[TR]
[TD="align: right"]08/05/2017[/TD]
[TD="align: right"]132955[/TD]
[TD="align: right"]12.07[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD="align: right"]132956[/TD]
[TD="align: right"]12.08[/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017
[/TD]
[TD="align: right"]132957[/TD]
[TD="align: right"]12.08
[/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017
[/TD]
[TD="align: right"]132958[/TD]
[TD="align: right"]11.98[/TD]
[/TR]
</tbody>[/TABLE]

Should I plough on? My next measure would be to work out the Max, then AVERAGEX MAX- MIN.
 
Sort by date Sort by votes
Code: 
Average Moving Range Selected=
 VAR
varMR=[Max of last two]-[Min of last two] 

RETURN
 IF(ISBLANK( [Moving Range] ),
    BLANK(),
    SUMX(
        ALLSELECTED ( QryAnalysis[Sample Date] ),
        varMR
    )
)
 
Upvote 0
Unfortunately:

[TABLE="width: 749"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sample Date[/TD]
[TD]Colour Group[/TD]
[TD]Average Value[/TD]
[TD]Average Selected values[/TD]
[TD]Starting Index[/TD]
[TD]Min of last two[/TD]
[TD]Max of last two[/TD]
[TD]Moving Range[/TD]
[TD]Average Moving Range Selected[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.79[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.87[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.79[/TD]
[TD="align: right"]11.87[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]194.64[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.20[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD="align: right"]12.2[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]24.33[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2017[/TD]
[TD]Green[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]0.09[/TD]
[TD="align: right"]218.97[/TD]
[/TR]
[TR]
[TD="align: right"]19/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]12.30[/TD]
[TD="align: right"]0.28[/TD]
[TD="align: right"]681.24[/TD]
[/TR]
[TR]
[TD="align: right"]22/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]24/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]12.02[/TD]
[TD="align: right"]0.06[/TD]
[TD="align: right"]145.98[/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]24.33[/TD]
[/TR]
[TR]
[TD="align: right"]06/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.97[/TD]
[TD="align: right"]0.13[/TD]
[TD="align: right"]316.29[/TD]
[/TR]
[TR]
[TD="align: right"]12/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.84[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]0.12[/TD]
[TD="align: right"]291.96[/TD]
[/TR]
[TR]
[TD="align: right"]19/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]11.96[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]48.66[/TD]
[/TR]
[TR]
[TD="align: right"]26/06/2017[/TD]
[TD]Flint[/TD]
[TD="align: right"]11.90[/TD]
[TD="align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.9[/TD]
[TD="align: right"]11.94[/TD]
[TD="align: right"]0.04[/TD]
[TD="align: right"]97.32[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Well that was pretty dumb as that's exactly what I just said wouldn't work. I am not sure I can understand why the outputs are what they are in that column so not sure my fix below is going to help.

Code: 
Average Moving Range Selected=
IF(ISBLANK( [Moving Range] ),
    BLANK(),
    SUMX(
        ALLSELECTED ( QryAnalysis[Sample Date] ),
        CALCULATE ( [Max of last two]-[Min of last two] )
    )
)

What do you get from COUNTROWS ( QryAnalysis[Sample Date] ), out of interest? You may want to post another thread as you are beyond my skill level and maybe no one else is reading this at this point.
 
Upvote 0
I'm fairly sure that's done it! (I've changed SUMX back to AVERAGEX as I was only using the Sum to see what was happening)
Results:
Flint
Flint
Green
Green
Green
Flint
Flint
Flint
Flint
Flint
Flint
Flint
Flint

<colgroup><col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> </colgroup><tbody>
[TD="class: xl70, width: 90"]Sample Date[/TD]
[TD="class: xl70, width: 106"]Colour Group[/TD]
[TD="class: xl70, width: 83"]Average Value[/TD]
[TD="class: xl70, width: 74"]Average Selected values[/TD]
[TD="class: xl70, width: 101"]Starting Index[/TD]
[TD="class: xl70, width: 65"]Min of last two[/TD]
[TD="class: xl70, width: 82"]Max of last two[/TD]
[TD="class: xl70, width: 73"]Moving Range[/TD]
[TD="class: xl70, width: 92"]Average Moving Range Selected[/TD]

[TD="class: xl71, align: right"]02/05/2017[/TD]

[TD="class: xl69, align: right"]11.79[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]

[TD="class: xl69"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]

[TD="class: xl71, align: right"]05/05/2017[/TD]

[TD="class: xl69, align: right"]11.87[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.79[/TD]
[TD="class: xl69, align: right"]11.87[/TD]
[TD="class: xl72, align: right"]0.080[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]09/05/2017[/TD]

[TD="class: xl69, align: right"]12.20[/TD]
[TD="class: xl69, align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]

[TD="class: xl69"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]

[TD="class: xl71, align: right"]10/05/2017[/TD]

[TD="class: xl69, align: right"]12.21[/TD]
[TD="class: xl69, align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD="align: right"]12.2[/TD]
[TD="class: xl69, align: right"]12.21[/TD]
[TD="class: xl72, align: right"]0.010[/TD]
[TD="class: xl72, align: right"]0.050[/TD]

[TD="class: xl71, align: right"]15/05/2017[/TD]

[TD="class: xl69, align: right"]12.30[/TD]
[TD="class: xl69, align: right"]12.24[/TD]
[TD="align: right"]135074[/TD]
[TD="align: right"]12.21[/TD]
[TD="class: xl69, align: right"]12.30[/TD]
[TD="class: xl72, align: right"]0.090[/TD]
[TD="class: xl72, align: right"]0.050[/TD]

[TD="class: xl71, align: right"]19/05/2017[/TD]

[TD="class: xl69, align: right"]12.02[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]12.02[/TD]
[TD="class: xl69, align: right"]12.30[/TD]
[TD="class: xl72, align: right"]0.280[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]22/05/2017[/TD]

[TD="class: xl69, align: right"]12.02[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]12.02[/TD]
[TD="class: xl69, align: right"]12.02[/TD]
[TD="class: xl72, align: right"]0.000[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]24/05/2017[/TD]

[TD="class: xl69, align: right"]11.96[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.96[/TD]
[TD="class: xl69, align: right"]12.02[/TD]
[TD="class: xl72, align: right"]0.060[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]29/05/2017[/TD]

[TD="class: xl69, align: right"]11.97[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.96[/TD]
[TD="class: xl69, align: right"]11.97[/TD]
[TD="class: xl72, align: right"]0.010[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]06/06/2017[/TD]

[TD="class: xl69, align: right"]11.84[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.84[/TD]
[TD="class: xl69, align: right"]11.97[/TD]
[TD="class: xl72, align: right"]0.130[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]12/06/2017[/TD]

[TD="class: xl69, align: right"]11.96[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.84[/TD]
[TD="class: xl69, align: right"]11.96[/TD]
[TD="class: xl72, align: right"]0.120[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]19/06/2017[/TD]

[TD="class: xl69, align: right"]11.94[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.94[/TD]
[TD="class: xl69, align: right"]11.96[/TD]
[TD="class: xl72, align: right"]0.020[/TD]
[TD="class: xl72, align: right"]0.082[/TD]

[TD="class: xl71, align: right"]26/06/2017[/TD]

[TD="class: xl69, align: right"]11.90[/TD]
[TD="class: xl69, align: right"]11.93[/TD]
[TD="align: right"]135072[/TD]
[TD="align: right"]11.9[/TD]
[TD="class: xl69, align: right"]11.94[/TD]
[TD="class: xl72, align: right"]0.040[/TD]
[TD="class: xl72, align: right"]0.082
[/TD]

</tbody>

I didn't run a COUNTROWS but I could see all the figures were being multiplied by 2433. I guess this was the number of Sample Dates being found when CALCULATE removed the higher level Pivot table filters operating that I haven't shown in my examples (YEAR, MONTH, Production Facility). I'm slightly reluctant to undo the latest changes to find out exactly; I hope you don't mind. You've certainly shown me a couple of uses of ALLSELECTED within an aggregate function that I hadn't read about, firstly qualifying the table name by column and secondly, what I still don't understand, is using SUMX without a Table name.
Many thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Calculating returns of a range using Product + Offset functions
2
Replies
13
Views
6K
duggie33
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top