Methods for assessing lag variables

[#1]

Do any of the gurus on here have an easy way to adjust columns based on some hypothesized lag.  Example situation: Trying to determine the impact of factors on sales, and some of the factors have been hypothesized to have a lag effect (e.g., 1 wk, 2 wk, 1 month, 1 qtr). So, for example, if we want to determine the effect of some variable (e.g., reviews, price, stars, etc.), but it takes "x" time (would like to easily vary this) for the variables change to take effect.  I basically have to shift the data in columns to see these lag effects...anyone got a better idea?  TIA!

[#2]

It would be very easy to setup new columns for the different weeks, or to setup a binning column for the different weeks, and then to analyze the data.  And that would be a good approach

[#3]

Hi,

There is a script that allows you to create lag columns save on the community ( Create multiple lag columns ).  Does that help?

[#4]

Thanks for your responses. I can setup new columns and shift the data manually (that's what I already do), but I was looking for a simple dialog that quickly lags a column a specified number of days/weeks/months/ etc. The script lets you start the lag after a specified number, but then goes by one row at a time.

[#5]

Can you please provide a description of you data table, or better yet, a sample of your data table.  Without that, one can only guess what your data are, and what JSL is required to setup the lag columns.

[#6]

Yes, of course.  First just a typical scenario...I am interested in the response variable of number of units sold (appliances).  I have this recorded in a column.  I have another column for statistics on housing starts.  I hypothesize that appliance sales do not occur at the housing start, but at some time interval multiple months from housing starts, so I want to lag the actual units sold column a number of months (and be able to vary this by different time series).  For the greatly reduced attached data set, there is a column of reviews, columns of "events", a column of units sold (Y) and Units sold lagged by 7 days (Yl).  The units don't actually sell when the events or reviews occur, but are lagged.  So the questions we are trying to answer are:

1. Do the events have a significant effect on units sold? If you correspond the units sold with the same row of the event, this assumes the sales occur simultaneously (on the same day) which may be non-sensical.

2. How much lag is there from "event" to change in units sold?  

[#7]

Here is a simple script that creates a new lag column based upon the a specified number of weeks.

Names Default To Here( 1 );
dt = Current Data Table();

dtTemp = dt << Subset(invisible, All rows, columns( :Date, :Yl ), output table("Lag Table") );
dtTemp:Yl << set name("4 Week Lag Y1");

dtTemp << New Column( "Lagged Date ", formula(:Date), format("m/d/y",10));

For Each Row(
	dtTemp:Date = dtTemp:Date + In weeks(4);
);

dtJoined = dt << Join(
	With( dtTemp ),
	By Matching Columns( :Date = :Date ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Merge Same Name Columns( 1 ),
	Match Flag( 0 )
);

close( dtTemp, nosave );

dtJointed = Bivariate(
	Y( :Yl ),
	X( :Name( "4 Week Lag Y1" ) ),
	Fit Line( {Line Color( "Medium Dark Red" )} )
);

[#8]

Jim,
Thanks for the effort. Perhaps I don't understand what the script is doing, but I just want the script to allow for selection of columns and to specify the number of weeks to create the new column. There are no options when I run this script. I don't want any particular analysis performed.

[#9]

The script is a simple example of how you can create a new data table with a 4 week lag in units sold.  The graph was just a simple plot of the original values vs. the lagged values.

The assumption of my script, was to provide you with the starting of a script that you could put together to meet your specific needs.

But it is not the solution if you are looking for someone in the Discussion Community to write for you a specific application.