Calculating Moving Avg. By Groups

unknown

I have a year's worth of daily sales volume of 8 items each identified by a code ItemNbr across 100 different stores each identified by a code StoreNbr. What I would like to do is calculate a 7 day moving average on an ItemNbr-StoreNbr basis. The moving avg. formula is as below. How can I group that on an ItemNbr-StoreNbr basis?

Any help would be greatly appreciated. Happy to share a copy of the file if that would be helpful.

MA Avg. Formula:
Mean(:POSQty[Row() - 3,Empty()], :POSQty[Row() - 2,Empty()], :POSQty[Row() - 1,Empty()], :POSQty[Row(),Empty()], :POSQty[Row() + 1,Empty()], :POSQty[Row() + 2,Empty()], :POSQty[Row() + 3,Empty()])

mpb

Not knowing how you plan to use the resulting summary info, I offer the following which may get you going in the right direction.

One way would be to get your data into a single data table with columns StoreNbr, ItemNbr, Day (from 1 to 365), and POSQty which is then sorted by StoreNbr, ItemNbr, and Day in that order. The table will contain 292,000 rows (8 x 100 x 365). Make a new column called Moving Average and insert your formula. This will give you the correct moving averages except for days 1, 2, 3 and 363, 364, 365. You can change your formula to be conditional on Day not being one of those six numbers and missing if it is.

If your original data has everything for a given day on a single line, then you should be able to get a stacked version of it using JMP's Table > Stack though it may take some effort depending on exactly how your data is structured.

From the same starting data table you could also use JMP's UWMA control chart by StoreNbr, and ItemNbr to generate 800 moving average charts and from them 800 summaries which could be put together to give the same summary table as above with some minor adjustment. This would seem to be quite unwieldy though. If you want to try something like this I would recommend experimenting with only a few stores worth of data.