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
- :
- 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

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

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

Jan 12, 2011 1:07 PM
(580 views)

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.