Calculate Moving Range Category Wise

[arun_tv]

I am creating an app for doing some spc calculations. Since I cannot share the real data, I have created dummy data here.
I have a table like below.

I am calculating the moving range and average of moving range for entire data.

Moving Range = fAbs(Sales - Above(total Sales))

MR Avg = Avg(Total Aggr(fabs(Above(sum(Sales))-sum(Sales)), [%KEY]))

This table calculates as expected.

I need a second table which should calculate moving range and average for individual item group.I later plan to plot this value against item groups.

Moving Range = Sum(Aggr(Above(total Sales),[%KEY]))

Expectation is that for individual item group, new moving range is created and calculations are done.But above formula calculates moving range according to the entire sales data.

All ideas are welcome.

[Brett_Bleess · Accepted solution]

@arun_tv I think you may need Set Analysis on this too, here are a couple of Design Blog links I hope will help:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett