# Re: st: How to roll up data with a time window and condition

 From "Eric G. Wruck" To statalist@hsphsun2.harvard.edu Subject Re: st: How to roll up data with a time window and condition Date Mon, 7 Nov 2005 14:10:28 -0500

```I saw that Nick gave you some suggestions that are probably far more elegant than my primitive approach but here goes:

. l, noobs

+------------------------------+
| company   year   qtr   sales |
|------------------------------|
|     ABC   1990     1      10 |
|     ABC   1990     2      11 |
|     ABC   1990     3      15 |
|     ABC   1990     4      10 |
|     ABC   1991     1      20 |
|------------------------------|
|     BBC   1989     1      20 |
|     BBC   1989     2      10 |
|     BBC   1989     3      20 |
|     BBC   1989     4      10 |
|     BBC   1990     1      15 |
|------------------------------|
|     BBC   1990     3      10 |
+------------------------------+

. gen sales4q = sales + sales[_n-1] + sales[_n-2] + sales[_n-3] if company == company[_n-3]
(6 missing values generated)

. l, noobs

+----------------------------------------+
| company   year   qtr   sales   sales4q |
|----------------------------------------|
|     ABC   1990     1      10         . |
|     ABC   1990     2      11         . |
|     ABC   1990     3      15         . |
|     ABC   1990     4      10        46 |
|     ABC   1991     1      20        56 |
|----------------------------------------|
|     BBC   1989     1      20         . |
|     BBC   1989     2      10         . |
|     BBC   1989     3      20         . |
|     BBC   1989     4      10        60 |
|     BBC   1990     1      15        55 |
|----------------------------------------|
|     BBC   1990     3      10        55 |
+----------------------------------------+

I typed one value in wrong (11 instead of 10) & so I'm off by one but otherwise I seem to produce the results you had in mind.  I assumed the data were sorted by company year & quarter.  I should point out that my method (& I don't know what Nick's suggestions would do on this score) disregards any gaps in the data.  For your BBC example, your data go from 1990.Q1 to 1990.Q3 -- a fact my <gen sales4q> statement ignores.

Hope this helps.

Eric

>I would like to roll up the past 4 quarters of sales for each company:
>
>Company  Year Quarters  Sales  4_Mo_Sales
>ABC      1990  1         10
>ABC      1990  2         10
>ABC      1990  3         15
>ABC      1990  4         10     45
>ABC      1991  1         20     55
>BBC      1989  1         20    BBC      1989  2         10  BBC
>1989  3         20
>BBC      1989  4         10     60
>BBC      1990  1         15     55
>BBC      1990  3         10     35
>
>The resulting calculation I am looking to get is in 4_Mo_Sales.
>
>Note the following:
>
>1) The first 3 quarters don't have 4 full quarters, so I don't calculate the 4_Mo_Sales for that 3 quarters
>
>2) For BBC, quarter 2 in 1990 is missing, so I only sum up 3 quarters and got 35 in the past 4 quarters
>
>Thanks so much!
>
>Lynda
>
>*
>*   For searches and help try:
>*   http://www.stata.com/support/faqs/res/findit.html
>*   http://www.stata.com/support/statalist/faq
>*   http://www.ats.ucla.edu/stat/stata/

--

===================================================

Eric G. Wruck
Econalytics
Columbus, OH  43209

ph:      614.231.5034
cell:    614.330.8846
eFax:    614.573.6639
eMail:   ewruck@econalytics.com
website: http://www.econalytics.com

====================================================
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```