Hi All,

I hope this is basic; just need a nudge in the right direction.

I have read in a database table from MS Access into a data frame using RODBC. Here is a basic structure of what I read in:

PRODID PROD Year Week QTY SALES INVOICE

Here is the structure:

str(data)
'data.frame': 8270 obs. of  7 variables:
 $ PRODID  : int  20001 20001 20001 100001 100001 100001 100001 100001 100001 100001 ...
 $ PROD    : Factor w/ 1239 levels "1% 20qt Box",..: 335 335 335 128 128 128 128 128 128 128 ...
 $ Year    : int  2010 2010 2010 2009 2009 2009 2009 2009 2009 2010 ...
 $ Week    : int  12 18 19 14 15 16 17 18 19 9 ...
 $ QTY     : num  1 1 0 135 300 270 300 270 315 315 ...
 $ SALES   : num  15.5 0 -13.9 243 540 ...
 $ INVOICES: num  1 1 2 5 11 11 10 11 11 12 ... 

Here are the top few rows:

head(data, n=10)
   PRODID           PROD Year Week QTY  SALES INVOICES
1   20001      Dolie 12" 2010   12   1  15.46        1
2   20001      Dolie 12" 2010   18   1   0.00        1
3   20001      Dolie 12" 2010   19   0 -13.88        2
4  100001 Cage Free Eggs 2009   14 135 243.00        5
5  100001 Cage Free Eggs 2009   15 300 540.00       11
6  100001 Cage Free Eggs 2009   16 270 486.00       11
7  100001 Cage Free Eggs 2009   17 300 540.00       10
8  100001 Cage Free Eggs 2009   18 270 486.00       11
9  100001 Cage Free Eggs 2009   19 315 567.00       11
10 100001 Cage Free Eggs 2010    9 315 569.25       12 

I simply want to generate lags for QTY, SALES, INVOICE for each product but I am not sure where to start. I know R is great with Time Series, but I am not sure where to start.

I have two questions:

1- I have the raw invoice data but have aggregated it for reporting purposes. Would it be easier if I didn't aggregate the data?

2- Regardless of aggregation or not, what functions will I need to loop over each product and generate the lags as I need them?

In short, I want to loop over a set of records, calculate lags for a product (if possible), append the lags (as they apply) to the current record for each product, and write the results back to a table in my database for my reporting software to use.

Any help you can provide will be greatly appreciated!

Many thanks in advance,

Brock