I am working on a report that shows Cumulative data.
The problem I'm running into is that weeks are skipped if there is no data for the week.
I need to show all weeks cumulatively even if there is no data.
Here is an example from CENTURYSALES to see the issue.

  
-* File pr_cumulative_sales_detail.fex

DEFINE FILE CENTURYSALES
ORDER_WEEK/HYYMDs = HDTTM(ORDERDATE,8,'HYYMDs');
WEEK/I2 = HPART(ORDER_WEEK,'WEEK','I2');
END

-* Is there are a way to see the Cumulative Sums for every Week 35,36,37,38,39 for every Model?
-*
-* Example 
-* MODEL     WEEK     CUMUL_QUANTITY
-* ---------------------------------
-* AVR-100   35       0
-* AVR-100   36       150
-* AVR-100   37       150
-* AVR-100   38       150
-* AVR-100   39       160
-*

TABLE FILE CENTURYSALES
SUM
    QUANTITY/I11  NOPRINT
    COMPUTE CUMUL_QUANTITY/I11 = IF (WEEK NE LAST WEEK) AND (MODEL EQ LAST MODEL) THEN (CUMUL_QUANTITY + QUANTITY) ELSE QUANTITY;
BY MODEL
BY WEEK
WHERE YEAR EQ '2005'
WHERE QUARTER EQ 'Q3'
WHERE MONTH EQ '09'
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN

This message has been edited. Last edited by: Mighty Max,

Look for "Specifying Your Own Sort Order" in the 'Creating Reports With WebFOCUS Language' documentation

Something like this:

BY WEEK ROWS 35 OVER 36 OVER 37 OVER 38 OVER 39

I have dynamically generated the missing records using two different methods.

If I need lots of row, I will use a McGuver join. This is using if an actuary needs monthly points in time for the past twenty years that is 240 rows.

The other one generate an include file as a save e.g.



TABLE FILE CENTURYSALES
PRINT
QUANTITY/I11 NOPRINT
COMPUTE CTR/I8 = LAST CTR + 1; NOPRINT
COMPUTE WEEK_NBR/I4 = IF CTR EQ 0 THEN 36 ELSE LAST WEEK_NBR + 1; NOPRINT
COMPUTE ROW_LINE/A10 = 'OVER ' | EDIT(WEEK_NBR);
ON TABLE SET HOLDLIST PRINTONLY
WHERE RECORDLIMIT EQ 11
ON TABLE SAVE
END

TABLE FILE CENTURYSALES
SUM
QUANTITY/I11 NOPRINT
COMPUTE CUMUL_QUANTITY/I11 = IF (WEEK NE LAST WEEK) AND (MODEL EQ LAST MODEL) THEN (CUMUL_QUANTITY + QUANTITY) ELSE QUANTITY;
BY MODEL
BY WEEK
WHERE YEAR EQ '2005'
WHERE QUARTER EQ 'Q3'
WHERE MONTH EQ '09'
ON TABLE SET ASNAMES ON
BY WEEK ROWS 35
-INCLUDE SAVE.FTM
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN



You can control the size save/include with Dialogue Manager and or where predicates.

BTW I have only enter the code here, not run it so there maybe syntax errors.

Thanks for the help guys.
Here is the solution I came up with using your techniques. Also, added option to pick start and end week number.
1. Fill in the missing weeks.
2. Calculate the Cumulative Sum.

  
-* File pr_cumulative_sales_detail.fex

-SET &ECHO = 'OFF';

-SET &STRT_WEEKNUM = 15;
-SET &ENDG_WEEKNUM = 39;

DEFINE FILE CENTURYSALES
ORDER_WEEK/HYYMDs = HDTTM(ORDERDATE,8,'HYYMDs');
WEEK/I2 = HPART(ORDER_WEEK,'WEEK','I2');
END

-* Create OVER for selected Start Week and End Week
TABLE FILE CENTURYSALES
SUM
    COMPUTE WEEK_NBR/D12 = DST.WEEK; NOPRINT
    COMPUTE ROW_LINE/A20 = 'OVER ' | FTOA(WEEK_NBR,'(D12)','A15');
BY WEEK NOPRINT
WHERE WEEK GT &STRT_WEEKNUM.EVAL
WHERE WEEK LE &ENDG_WEEKNUM.EVAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN

-* Fill in missing weeks using ROWS OVER syntax
TABLE FILE CENTURYSALES
SUM
    QUANTITY
BY MODEL
BY WEEK AS 'WEEK' ROWS &STRT_WEEKNUM.EVAL
-INCLUDE SAVE.FTM
WHERE YEAR EQ '2005'
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS H1
END
-RUN

-* WEEK becomes alpha in hold file H1 change back to int
DEFINE FILE H1
WEEK/I2 = EDIT(WEEK);
END

-* Calculate Cumulative Sum
TABLE FILE H1
SUM
    QUANTITY
    COMPUTE CUMUL_QUANTITY/I11 = IF (WEEK NE LAST WEEK) AND (MODEL EQ LAST MODEL) THEN (CUMUL_QUANTITY + QUANTITY) ELSE QUANTITY;
BY MODEL
BY WEEK
END