topic Re: Calculate Moving Average Using Last 3 Time Periods in Discussions
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38565#M22576
<P>The formula that I gave you takes a look at the year/month value for a given row. It then finds what the value 1 month prior is, and the value 2 months prior would be. It then finds the mean for all rows in the data table that have the year/month value for any of the 3 calculated year/month values.</P>
<P>Therefore, any row that has a year/month value of, let's say, <SPAN>201705, would find the average quanity of all of the rows in the data table that are found to have the value of 201705, 201704 or 201703. My limited test case worked for this. Please note, the physical order of the rows in the data table are not of importance. The formula finds all of the rows in the entire data table that match the 3 time periods for a given row.</SPAN></P>
<P><SPAN>If you could provide me with a sample data table, I can look further into this. Or, maybe my interpretation of what you are asking is not correct.</SPAN></P>Thu, 27 Apr 2017 13:13:26 GMTtxnelson2017-04-27T13:13:26ZCalculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38514#M22546
<P>I have a set of data with three columns; Customer, TimePeriod, and Quantity. TimePeriod is in the format of yyyymm. I'm trying to determine a formula to calculate a "rolling average" over the last 3 time periods, across all customers.</P>
<P> </P>
<P>For example, In my row labeled 201704, I want a formula to return the average Quantity for all records from 201703, 201702, and 201701. The TimePeriod can occur multiple times in the data (by Customer), which makes it a little more complicated.</P>
<P> </P>
<P>Any suggestions?</P>Wed, 26 Apr 2017 16:11:49 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38514#M22546Risslemc2017-04-26T16:11:49ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38525#M22553
<P>Here is your formula</P>
<PRE><CODE class=" language-jsl">dt = Current Data Table();
tp = :timeperiod;
tmp = Date Increment(
Date MDY( Num( Substr( :timeperiod, 5, 2 ) ), 1, Num( Substr( :timeperiod, 1, 4 ) ) ),
"Month",
-1
);
lagtp1 = Char( Year( tmp ) ) || Try( Substr( "0", Length( Char( Month( tmp ) ) ), 1 ), "" ) ||
Char( Month( tmp ) );
tmp = Date Increment(
Date MDY( Num( Substr( :timeperiod, 5, 2 ) ), 1, Num( Substr( :timeperiod, 1, 4 ) ) ),
"Month",
-2
);
lagtp2 = Char( Year( tmp ) ) || Try( Substr( "0", Length( Char( Month( tmp ) ) ), 1 ), "" ) ||
Char( Month( tmp ) );
Mean(
:Quantity[dt << get rows where( :timeperiod == tp | :timeperiod == lagtp1 | :timeperiod == lagtp2 )
]
);</CODE></PRE>Wed, 26 Apr 2017 18:00:49 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38525#M22553txnelson2017-04-26T18:00:49ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38529#M22556
<P>Thank you very much for your reply.</P><P> </P><P>I'm very new to scripting. Is there a way for the mean to be output in a new column, by row? So as the script determines the mean over the last 3 timeperiods it will display that for each row in a new column?</P><P> </P><P>Additionally, is there an issue with that script at the beginning of the data set? When there's no lagtp1 or lagtp2 for the very first date? </P>Wed, 26 Apr 2017 20:40:43 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38529#M22556Risslemc2017-04-26T20:40:43ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38530#M22557
<P>What I gave you is a new formula that is intended to be used in a new column, that will create a new value for each row.</P>
<P>In the data table, go to the pull down menu</P>
<P> Cols==>New Column</P>
<P>Name the column whatever you want</P>
<P>In the lower left hand corner, click on</P>
<P> Column Properties</P>
<P>Select Formula</P>
<P>Double click on the blue box that says "No Formula"</P>
<P>In the window that opens, paste in the formula</P>
<P> </P>
<P> </P>Wed, 26 Apr 2017 20:46:17 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38530#M22557txnelson2017-04-26T20:46:17ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38559#M22574
<P>Jim - Thank you for your patience.</P><P> </P><P>I added that formula into a new column, as you guided, but I get the same repeating value down the whole column.</P><P>I would like the formula to just give me the mean for the last 3 time periods <STRONG>of that row</STRONG>.</P><P> </P><P>For example, the Mean record from 201704 should only average the last 3 time periods from April 2017 (201704, 201703, 201702). The next row from 201705 should have a different average consisting of the last 3 time periods from May 2017 (201705, 201704, 201703).</P><P> </P><P>Thoughts? </P><P> </P>Thu, 27 Apr 2017 12:50:37 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38559#M22574Risslemc2017-04-27T12:50:37ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38565#M22576
<P>The formula that I gave you takes a look at the year/month value for a given row. It then finds what the value 1 month prior is, and the value 2 months prior would be. It then finds the mean for all rows in the data table that have the year/month value for any of the 3 calculated year/month values.</P>
<P>Therefore, any row that has a year/month value of, let's say, <SPAN>201705, would find the average quanity of all of the rows in the data table that are found to have the value of 201705, 201704 or 201703. My limited test case worked for this. Please note, the physical order of the rows in the data table are not of importance. The formula finds all of the rows in the entire data table that match the 3 time periods for a given row.</SPAN></P>
<P><SPAN>If you could provide me with a sample data table, I can look further into this. Or, maybe my interpretation of what you are asking is not correct.</SPAN></P>Thu, 27 Apr 2017 13:13:26 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38565#M22576txnelson2017-04-27T13:13:26ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38584#M22584
<P>You were correct! It must have been user error on my end :-)</P><P>If I want the means separated by customer, let's say 'Customer' is in its own column to reference, is that possible?</P><P>So then the output column will only calculate the mean for the last 3 timeperiods for the customer in that row.</P><P> </P><P> </P>Thu, 27 Apr 2017 15:10:36 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38584#M22584Risslemc2017-04-27T15:10:36ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38589#M22586
<P>Here is the extension of the formula with the Customer added in</P>
<PRE><CODE class=" language-jsl">dt = Current Data Table();
tp = :timeperiod;
cus = :Customer;
tmp = Date Increment(
Date MDY( Num( Substr( :timeperiod, 5, 2 ) ), 1, Num( Substr( :timeperiod, 1, 4 ) ) ),
"Month",
-1
);
lagtp1 = Char( Year( tmp ) ) || Try( Substr( "0", Length( Char( Month( tmp ) ) ), 1 ), "" ) ||
Char( Month( tmp ) );
tmp = Date Increment(
Date MDY( Num( Substr( :timeperiod, 5, 2 ) ), 1, Num( Substr( :timeperiod, 1, 4 ) ) ),
"Month",
-2
);
lagtp2 = Char( Year( tmp ) ) || Try( Substr( "0", Length( Char( Month( tmp ) ) ), 1 ), "" ) ||
Char( Month( tmp ) );
Mean(
:Quantity[dt << get rows where( ( :timeperiod == tp | :timeperiod == lagtp1 | :timeperiod == lagtp2 ) &
:Customer == cus)
]
);</CODE></PRE>Thu, 27 Apr 2017 16:41:31 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38589#M22586txnelson2017-04-27T16:41:31ZRe: Calculate Moving Average Using Last 3 Time Periods
https://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38591#M22587
<P>Thank you again for all your help!</P><P> </P><P>That's exactly what I was looking for.</P><P> </P><P>-Matt</P>Thu, 27 Apr 2017 16:46:51 GMThttps://community.jmp.com/t5/Discussions/Calculate-Moving-Average-Using-Last-3-Time-Periods/m-p/38591#M22587Risslemc2017-04-27T16:46:51Z