nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Calculate Moving Average Using Last 3 Time Periods

Risslemc

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.

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.

Any suggestions?

Accepted answers

txnelson

Here is your formula

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

All comments

txnelson

Here is your formula

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

Risslemc

Thank you very much for your reply.

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?

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?

txnelson

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.

In the data table, go to the pull down menu

Cols==>New Column

Name the column whatever you want

In the lower left hand corner, click on

Column Properties

Select Formula

Double click on the blue box that says "No Formula"

In the window that opens, paste in the formula

Risslemc

Jim - Thank you for your patience.

I added that formula into a new column, as you guided, but I get the same repeating value down the whole column.

I would like the formula to just give me the mean for the last 3 time periods of that row.

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).

Thoughts?