Hopefully this is a simple question. I am computing an average, but the average is not including the days that have a null volume. So instead of computing the average for January using 31 days, the average is only using 16 days. How do I include the null records? The code looks like this..
Thank You!

TABLE FILE SQLOUT
SUM
LEF_VOLUME/P12C AS 'LEF'
GCSS_VOLUME/P12C AS 'GCSS'
SSWT_VOLUME/P12C AS 'SSWT'
BY FOR_MONTH NOPRINT
BY FOR_DATE
ON FOR_MONTH SUBTOTAL AVE. * AS 'Average '

I'm reading here you want to include 'nulls' as zero and include zero in the averaging process. Also looks like some code missing here. Only way I can think of is actually compute the average by dividing the 'summed' value by the number of days in the month, which you might have to compute as well.

Alternative is to used a version of the famed McGyver technique.

Create a temporary file with only a date field and populate it with the dates within the month in which you are interested. Then left_outer join the date field in this file to the date field in your data file, sorting on the output on the date field from the parent.

I have used this method to provide a calendar showing absences, colour coded for type of absence.

A quick piece of code to create a temporary FOCUS file for one month is -

FILEDEF DATEMAS DISK DATERNG.MAS
-RUN
-WRITE DATEMAS FILE=DATERNG,SUFFIX=XFOC
-WRITE DATEMAS SEGNAME=SEG1
-WRITE DATEMAS FIELD=DATE_KEY, ,DMYY ,DMYY , $
-RUN
-* Now create it
CREATE FILE DATERNG
-* and add all possible dates within a month
MODIFY FILE DATERNG
FREEFORM DATE_KEY.A8.
LOG FORMAT MSG OFF
LOG TRANS MSG OFF
LOG INVALID MSG OFF
DATA
-* This repeat loop will ensure at least a whole year is input,
-* Any invalid dates will be rejected.
-DEFAULT &Year  = 2008
-SET &Year  = EDIT(&YYMD,'9999$');
-SET &Month = EDIT(&YYMD,'$$$$99$');
-SET &Day   = 1;
-REPEAT :Loop1 31 TIMES;
-SET &Date = IF &Day LT 10 THEN '0' || &Day ELSE &Day;
&Date&Month&Year
-SET &Day = &Day + 1;
-:Loop1
END
-RUN
TABLE FILE DATERNG
PRINT *
END
-RUN

Note that I repeat the loop 31 times as the max number of days in a month. Because the date field format is a smart date, the process rejects as invalid the dates that are incorrect.

T

Here's one way to do it without looping.

-*Create MFD
UNIX echo "FILE=AFILE, SUFFIX=FIX, $" > /eda53/ibi/apps/masters/afile.mas
UNIX echo "SEGNAME=SPACE,  SEGTYPE=S0, $" >> /eda53/ibi/apps/masters/afile.mas
UNIX echo "FIELD=DATE,     ALIAS=E00,   USAGE=I8YYMD, ACTUAL=A8,  $" >> /eda53/ibi/apps/masters/afile.mas
UNIX echo "FIELD=PRODUCT,  ALIAS=E01,   USAGE=A8,   ACTUAL=A8,  $" >> /eda53/ibi/apps/masters/afile.mas
UNIX echo "FIELD=QUANTITY, ALIAS=E01,   USAGE=I4,   ACTUAL=A4,  $" >> /eda53/ibi/apps/masters/afile.mas
-*Create AFILE
UNIX echo "20080101Apples    10" > /edadata/afile
UNIX echo "20080101Oranges    9" >> /edadata/afile
UNIX echo "20080102Apples    21" >> /edadata/afile
UNIX echo "20080103Oranges    9" >> /edadata/afile
UNIX echo "20080104Apples    19" >> /edadata/afile
UNIX echo "20080105Apples    22" >> /edadata/afile
UNIX echo "20080106Oranges   17" >> /edadata/afile
UNIX echo "20080107Apples     8" >> /edadata/afile
UNIX echo "20080108Apples    12" >> /edadata/afile
UNIX echo "20080109Oranges   21" >> /edadata/afile
UNIX echo "20080110Apples    13" >> /edadata/afile
FILEDEF AFILE DISK /edadata/afile
-*
DEFINE FILE AFILE
MOYR/YYM = DATE;
END
-*
TABLE FILE AFILE
SUM
    QUANTITY
BY PRODUCT
BY MOYR 
ON TABLE HOLD AS FDATA
END
-*
DEFINE FILE FDATA
MONTH/MONTR    = MOYR;
YEAR/YY        = MOYR;
REMAIN/I3L     = IMOD(YEAR, 4, 'I3L');
LEAPYEAR/A1    = IF IMOD(YEAR, 4, 'I3L')=0 THEN 'Y' ELSE 'N';
DOM/I2         = IF MONTH IN ('APRIL', 'JUNE', 'SEPTEMBER', 'NOVEMBER') THEN  30 ELSE
                 IF MONTH EQ 'FEBRUARY' THEN 28 ELSE 31;
DAYS/I2        = IF LEAPYEAR EQ 'Y' AND MONTH EQ 'FEBRUARY' THEN 29 ELSE DOM;
AVG_QUAN/D12.2 = QUANTITY/DAYS;
END
-*
TABLE FILE FDATA
PRINT MOYR AS MONTH
      PRODUCT
      QUANTITY AS 'MONTHLY SALES TOTAL'
      AVG_QUAN AS 'AVG SALES PER DAY'
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*Remove AFILE
UNIX rm /edadata/afile
-*Remove MFD
UNIX rm /eda53/ibi/apps/masters/afile.mas
-EXIT