Does anyone have a good technique for creating a rolling 12 month avergae from monthly data?

This message has been edited. Last edited by: <Kathryn Henning>,

Yes I have, but will have to check my business computer tomorrow.
I will try to convert it a bit to the CAR database and then send you the script.

Have a look at FML, Adelaide, its totally swell for this sort of thing.
In the meantime,

TABLE FILE CAR
SUM SALES
COMPUTE CSALES/I8S=SALES + LAST CSALES;
COMPUTE KOUNT/I8S=IF MODEL EQ LAST MODEL THEN LAST KOUNT ELSE 1 + KOUNT;
AND COMPUTE RAVG/I8S=C2/C4 ;
BY MODEL
END
...why C4, and not C3? 'cause the C3 value is hidden, part of the compute, its actually the MODEL field, used in the compute.

Susannah,

I think your solution will only give the average and not a rolling or moving average.
And my second remark is that this is not the solution for a graph report. FML is only character based reporting as far as I know.

I use this on a database that hold the montly sales per salesmanager.

I first do a query on that table

DEFINE FILE TOTALSALES
BEGIN12MONTH/YYMD=DATEADD(MONTHPERIOD,'M',-12);
END
TABLE FILE TOTALSALES
PRINT FIRST.BEGIN12MONTH
BY MONTHPERIOD
ON TABLE HOLD AS HOLDBASE FORMAT ALPHA
END
-RUN
-* this table would look like
2005010120040101
2005020120040201
2005030120040301
2005040120040401
2005050120040501
2005060120040601
etc
-*
-LABELONE
-READ HOLDBASE &MONTHVAL.A8. &BEGINDATE.A8. NOCLOSE
-IF &IORETURN NE 0 GOTO RESUME;
-* now reread the totalsales
-
DEFINE FILE TOTALSALES
TWELFMONTHSALES/D15=IF MONTH GT '&BEGINDATE' AND MONTH LE '&MONTHVAL' THEN SALES ELSE 0;
MONTHSALES/D15=IF MONTH EQ '&MONTHVAL' THEN SALES ELSE 0;
PERIOD/YYMD='&MONTHVAL';
END
TABLE FILE TOTALSALES
SUM MONTHSALES
    TWELFMONTHSALES
    COMPUTE AVESALES=TWELFMONTSALES/12;  
BY SALESMANAGER
BY PERIOD
ON TABLE HOLD AS HOLDDATA (NOCLOSE
END
-* Here I need a smart looping until the last record from the HOLDBASE
-GOTO LABELONE
-* Now you can use the holddata table for printing of graphing 
-RESUME
-CLOSE HOLDBASE
-*
TABLE FILE HOLDDATA
SUM MONTHSALES
      AVESALES
COMPUTE PERC/D12.1%=(AVESALES-MONTHSALES)/MONTHSALES*100; 
BY SALESMANAGER
BY PERIOD
WHERE PERIOD GE '&STARTDATE';;
WHERE SALESMANAGER EQ '&MANAGER';
END

I use this in a graph per manager that shows a bar with the sales data for each month and a line with the moving average value.

I hope this is helpfull

Frank

Greg,

Are you acquainted with the McGuyver Techniques?

If not, I can send you a ppt. Just ask.

As for you question here is an elegant solution using the McGuyver. I created a small file with months and values using the (in)famous CAR file.

DEFINE FILE CAR
MO/MYY='01/2006';
I/I2 WITH SALES =1 + LAST I;
MONTH/MYY=MO + I - 1;
END
TABLE FILE CAR
PRINT MONTH SALES
ON TABLE HOLD AS XXX
END
-RUN

You will have to use a McGuyver file. I call it FSEQ. See the MASTER and the DATA

FSEQ.mas
FILENAME=FSEQ,SUFFIX=FIX
SEGNAME=CHAR1,SEGTYPE=S0
FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
FIELDNAME=CHAR,CHARS,A1,A1,$
FIELDNAME=COUNTER,ORDER,I2,I4,$

FSEQ.dat (notice that it starts with a space and it can be as long as you want)
FILEFORMCGUYVERFILEFORMCGUYVER

The solution for a 6-month moving average:

JOIN BLANK WITH MONTH IN XXX TO BLANK IN FSEQ AS A_
DEFINE FILE XXX
BLANK/A1 WITH MONTH = ' ';
XMONTH/MYY=MONTH + COUNTER - 1;
END
TABLE FILE XXX
SUM AVE.SALES
BY XMONTH
IF COUNTER LE 6
END

Daniel

that is an interesting solution, but if this does the same as mine ? I will let you know soon.

Frank

This will give you a moving average. You may want to add some NOPRINT's. The pattern can be followed to inclease the number of values included in the average.

TABLE FILE EMPLOYEE
PRINT
COMPUTE CTR/I4 = LAST CTR + 1;
GROSS
COMPUTE LAG_1/D12.2S = LAST GROSS;
COMPUTE LAG_2/D12.2S = LAST LAG_1;
COMPUTE LAG_3/D12.2S = LAST LAG_2;
AVER/D12.2S = IF CTR LT 4 THEN 0 ELSE
(GROSS + LAG_1 + LAG_2 + LAG_3) / 4;
AS 'ROLL,AVE,4)
END

I should have clarified that I need a rolling 12 month average (not 2 months).

Susannah I am well versed in FML but cannot see how it will assist with a rolling 12 month average, 2 months is easy because of the LAST facility but I need the last 11 values to add to the current value so I can divide by 12 and get the result I need.

I have an idea but was interested to see if anyone else had a clever solution for this requirement.

Thanks Frank and Jim and Daniel.
I'll try these out and pick the one that suits best.
I am a veteran of McGuyver since it was first published in the FOCUS Systems Journal way back when.

Try my solution OPALTHOS, I'm sure this is what you need...12 months moving average...
The others just calculate the average.

Do you want the numbers for each of the last twelve consecutive months or years?

Frank,
You should look at Jim's solution. It is exactly what I wanted and the code is much simpler. It worked first time and it is indeed a rolling 12 month average.

I have not tried Daniel's McGuyver techniques as yet - I have not quite got my head around it.

oh duh, right. just forget what i said.
its only a cumulative average.
i didn't process the '12 month' bit...

but Frank, FML is HTML as well, i have it extensively in my site. Is that what you meant by 'character based'?

Thanks Jim,
Your solution worked a treat! It's easy to understand and the code is small.
I've churned out 20 graphs today most of which had one or two rolling 12 month averages on them.

quote:

TABLE FILE EMPLOYEE
PRINT
COMPUTE CTR/I4 = LAST CTR + 1;
GROSS
COMPUTE LAG_1/D12.2S = LAST GROSS;
COMPUTE LAG_2/D12.2S = LAST LAG_1;
COMPUTE LAG_3/D12.2S = LAST LAG_2;
AVER/D12.2S = IF CTR LT 4 THEN 0 ELSE
(GROSS + LAG_1 + LAG_2 + LAG_3) / 4;
AS 'ROLL,AVE,4)
END

Yes this works indeed and is less complex...
I will try to build this in my reports and see if I can use it as needed.

Frank

I tried to use it, but what if I want to do it for a moving 100 days and 200 days average comparison.
I'm afraid that would end up into a complex define.
What we infact need is the old ANALYSE function from PC-Focus...

i second that!
How about this:
for your 100 days avg,define a single new field
that is MISSING if the date is > 100 days ago;
If you then average that value, you have only 100 observations and the missings won't count.
If that works for you, repeat with a second new field that is missing if date > 200 days ago.
For the current day, you'll always have your last n days. If you need a time series, calculate each night and store in a db, so your history graphs are faster.

This message has been edited. Last edited by: susannah,

Frank,

If you use the McGuyver method then the only thing you need is a long string in the data file and a parameter in the IF COUNTER statement: IF COUNTER EQ &months

Also if you only want those months where there are &months values and not those where there are less, the TABLE request looks like this:

TABLE FILE XXX
SUM AVE.SALES
CNT.SALES NOPRINT
BY XMONTH
IF COUNTER LE &months
IF TOTAL CNT.SALES EQ &months
END

This message has been edited. Last edited by: Danny-SRL,

I know that this is an incredibly old thread, but wanted to chime in and say that I just applied Jim's technique in InfoAssist and it worked like a charm.

WF FORECAST processing will also generate moving averages.

Both Jim's technique, and using FORECAST yield the same numbers for me.

It is likely FORECAST wasn't available at the time of the post.

Classic:

TABLE FILE EMPLOYEE
PRINT
COMPUTE CTR/I4 = LAST CTR + 1;
        GROSS
COMPUTE LAG_1/D12.2S = LAST GROSS;
COMPUTE LAG_2/D12.2S = LAST LAG_1;
COMPUTE LAG_3/D12.2S = LAST LAG_2;
COMPUTE AVER/D12.2S = IF CTR LT 4 THEN 0 ELSE
           (GROSS + LAG_1 + LAG_2 + LAG_3) / 4;
             AS 'ROLL,AVE,4'
END  

New:

DEFINE FILE EMPLOYEE
 CTR/I4 WITH GROSS = LAST CTR + 1;
END
TABLE FILE EMPLOYEE
SUM   GROSS
COMPUTE MOVAVE/D10.2 = ; AS 'ROLL,AVE,4'
BY CTR
ON CTR RECAP MOVAVE/D10.2 = FORECAST(GROSS, 1, 4, 'MOVAVE', 4); 
IF TOTAL LAST_NAME NE ' '
END  

Excellent, post Melanie, following up on this thread. For sure, no reason for you to switch from 'classic' to 'new', I'm just following up, like you did. :-)

One way to do this is to create a file that has the cumulative sum of each month (or day in the 100-day scenario), with enough history that you can go 12 months (or 100 days) back.

To get the rolling sum of 12 months or 100 days, you take the cumulative of the desired month (or day) and subtract the one from 12 months (or 100 days) ago. You'll need to use the file twice for that, of course.

For the historic cumulative (the one to subtract from the current), we usually adjust the date of the month/day such that it's the same as the current one, but with data from 12 months/100 days ago. Having that, you can apply this trick for a range of dates at once.

It gets a little more difficult if you have gaps in your data, but you can fill those with MacGyver.

Here's our trick:

I wanted to make a graph with 52 weeks ( bars ).
Each bar should represent the average turnover of that specific week and it's 51 predecessors.

1. Query the last 104 weeks turnover and add a 'row'-counter. 1 to 104. -> HOLDFILE_01

2. startloop : &loop_counter = 1;

3. Query HOLDFILE_01
WHERE row_counter GE &loop_counter
WHERE row_counter LE ( &loop_counter + 51 )
HOLD AS H_&loop_counter

4. &loop_counter = &loop_counter + 1;

5. if loop_counter lt 52 goto startloop;

6. TABLE FILE H_1
ON TABLE HOLD AS H_MAT
( don't add an END here )

7. startmore : &more_counter = 2;

8.
MORE
FILE MAT_&more_counter

9. &loop_counter = &loop_counter + 1;

10. if loop_counter lt 52 goto startmore;

11. END


...

Thanks for the tip on the forecast function, David. I'm not a coder so I typically start in InfoAssist and then only move over to AppStudio when InfoAssist wont do what I need, so I was unaware of it.

This forecast feature is some powerful magic :-) and will be so much easier when you need to average over many periods.

I'm also dealing with gaps in my data as Wep5622 mentions above, so will need to investigate MacGyver...

Fully working example

-* FIRST MAKE A DATASET WITH FAKE WEEKNUMBER
TABLE FILE CAR
	SUM COMPUTE WEEK/I2 = WEEK + 1;
		SALES
	BY CAR
ON TABLE HOLD AS DATAFILE
END

-* I'll be looking 3 weeks back, so start I need to start with week 3 to be able to look back

-SET &LOOP_COUNTER = 3

-LOOP_START_QUERY

TABLE FILE DATAFILE
	SUM SALES
		MAX.WEEK AS 'WEEK'
WHERE WEEK LE &LOOP_COUNTER
WHERE WEEK GE ( &LOOP_COUNTER - 3 );
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS MAT_&LOOP_COUNTER
END

-TYPE MAT_&LOOP_COUNTER

-SET &LOOP_COUNTER = &LOOP_COUNTER + 1;

-IF &LOOP_COUNTER LE 10 THEN GOTO LOOP_START_QUERY ELSE CONTINUE;

-* Reset counter, but 1 higher because the first shouldn't be in the loop

-SET &LOOP_COUNTER = 3

-* FIRST TABLE OUTSIDE THE LOOP
TABLE FILE MAT_&LOOP_COUNTER
	SUM SALES
	BY	WEEK
ON TABLE PCHOLD FORMAT HTML

-LOOP_START_TABLE

MORE
FILE MAT_&LOOP_COUNTER

-SET &LOOP_COUNTER = &LOOP_COUNTER + 1;

-IF &LOOP_COUNTER LE 10 THEN GOTO LOOP_START_TABLE ELSE CONTINUE;

END