Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Calculating Moving Avg. By Groups

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Calculating Moving Avg. By Groups

Jan 11, 2011 7:21 PM
(1185 views)

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()])

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()])

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating Moving Avg. By Groups

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.

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.