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
- :
- Calculate moving average By-Group and Subgroup

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 30, 2014 9:32 AM
(6756 views)

Hi,

Building off the concept of calculating a moving average for a column named By-group, can someone help me figure out how to do this by multiple groups.

The input column is called Guest_count.

My group(s) consists of Store_Num, Day_of_the_Year, Hour

I want to create a moving average forecast of Guest_count from the last 3 previous values (for each Store_Num,Hour,DayOfYear) for Jan to Dec 2014. I have actual data for Jan through April 2014.

Can someone help me with the JMP formula for this.

1 ACCEPTED SOLUTION

Accepted Solutions

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

assuming your data are in sorted order, the following formula will produce what you want

If(Row() == 1, thecount = 1);

If(:Store_Num == Lag(:Store_Num, 1) & :Day_of_the_Year == Lag(:Day_of_the_Year, 1) & :Hour == Lag(:Hour, 1), thecount = thecount + 1, thecount = 1);

If(thecount >= 3, x = Mean(:Guest_Count, Lag(:Guest_count, 1), Lag(:Guest_Count, 2)), x = .);

x

Jim

2 REPLIES 2

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

assuming your data are in sorted order, the following formula will produce what you want

If(Row() == 1, thecount = 1);

If(:Store_Num == Lag(:Store_Num, 1) & :Day_of_the_Year == Lag(:Day_of_the_Year, 1) & :Hour == Lag(:Hour, 1), thecount = thecount + 1, thecount = 1);

If(thecount >= 3, x = Mean(:Guest_Count, Lag(:Guest_count, 1), Lag(:Guest_Count, 2)), x = .);

x

Jim

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

Re: Calculate moving average By-Group and Subgroup

Thank you. I was able to use this code to solve my problem.