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.
Go to Solution
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 = .);
Thank you. I was able to use this code to solve my problem.