Hello everyone.

This is what I am trying to attempt:

2010 2011 2012 2013 2014 2015 %Chg
Bananas $1.00 1.25 1.27 1.28 1.35 1.5 50%
Eggs 0.89 0.99 1.01 1.03 1.04 1.1 24%
Milk 2.12 2.25 2.37 2.49 2.68 2.75 30%

Totals 4.01 4.49 4.65 4.8 5.07 5.35 33%

Right now my code (below) totals the columns correctly, but I get 103% for the five-year percentage of change instead of 33%.

This will change depending on the year selected in the dropdown box.

I know I need to use recompute or recap. I have looked at several posts including:
-[SOLVED] Rolling Average
-[SOLVED] how to calculate percentage in subtotal
- RECAP/RECOMPUTE PERCENTAGES

None of them quite do what I need, unless I am missing something altogether. Does anyone have any thoughts? Am I just overlooking the obvious?

-* File: IBFS:/DEV81/WFC/Repository/BIP_Public2/SALES/Report2.fex Created by WebFOCUS AppStudio
TABLE FILE SALES
SUM
     SALES.SALES.R_CATEGORY_NO
     SALES.SALES.CATEGORY_TOTAL/P11M
     COMPUTE LAG_1/D12.2 = IF STORE EQ LAST STORE THEN LAST CATEGORY_TOTAL ELSE 0;
     COMPUTE LAG_2/D12.2 = IF STORE EQ LAST STORE THEN LAST LAG_1 ELSE 0;
     COMPUTE LAG_3/D12.2 = IF STORE EQ LAST STORE THEN LAST LAG_2 ELSE 0;
     COMPUTE LAG_4/D12.2 = IF STORE EQ LAST STORE THEN LAST LAG_3 ELSE 0;
     COMPUTE LAG_5/D12.2 = IF STORE EQ LAST STORE THEN LAST LAG_4 ELSE 0;
     COMPUTE PERCENT_LAG_5/D10.1% = IF LAG_5 NE 0 THEN ( CATEGORY_TOTAL - LAG_5 ) / LAG_5 * 100 ELSE 0; AS 'Five Year Diff'
BY  LOWEST SALES.SALES.REGION NOPRINT
BY  LOWEST SALES.SALES.STORE
BY  LOWEST SALES.SALES.CAT_NAME
BY  LOWEST SALES.SALES.SALES_YEAR

ON SALES.SALES.STORE RECOMPUTE AS '*TOTAL'
WHERE ( SALES.SALES.FIN_NAME EQ 'All Sales by ACCOUNTS' );
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest).Region:.' );
WHERE ( SALES.SALES.STORE EQ '&STORE.(FIND SALES.SALES.STORE IN SALES).Store:.' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS ACCOUNTS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

TABLE FILE ACCOUNTS
SUM
     LST.ACCOUNTS.ACCOUNTS.PERCENT_LAG_5 NOPRINT  AS 'Lag 5'
BY  ACCOUNTS.ACCOUNTS.STORE AS 'Store'
BY  ACCOUNTS.ACCOUNTS.R_CATEGORY_NO NOPRINT
BY  ACCOUNTS.ACCOUNTS.CAT_NAME AS 'Category'
SUM
     ACCOUNTS.ACCOUNTS.CATEGORY_TOTAL/P11M AS ' '
BY  ACCOUNTS.ACCOUNTS.STORE AS 'Store'
BY  ACCOUNTS.ACCOUNTS.R_CATEGORY_NO NOPRINT
BY  ACCOUNTS.ACCOUNTS.CAT_NAME AS 'Category'
ACROSS ACCOUNTS.ACCOUNTS.SALES_YEAR AS 'Sales Year'
     COMPUTE LAG_5/D10.1% = LST.PERCENT_LAG_5; AS '5 Yr Chg'

ON ACCOUNTS.ACCOUNTS.STORE RECOMPUTE 
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE RECOMPUTE AS 'TOTAL'
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel 2007,XLSX>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN  

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

Julie,

If you want your % recomputed properly it must be COMPUTEd in the last TABLE FILE.

Meaning that

COMPUTE PERCENT_LAG_5/D10.1% = IF LAG_5 NE 0 THEN ( CATEGORY_TOTAL - LAG_5 ) / LAG_5 * 100 ELSE 0; AS 'Five Year Diff'

must be performed in TABLE FILE ACCOUNTS not in TABLE FILE SALES

TABLE FILE SALES
SUM  UNIT_SOLD
     RETAIL_PRICE
     COMPUTE PCT_1/D10.1% = RETAIL_PRICE / UNIT_SOLD * 100;
BY STORE_CODE
BY CITY
ON TABLE HOLD AS ACCOUNTS
END

TABLE FILE ACCOUNTS
SUM  UNIT_SOLD
     RETAIL_PRICE
     COMPUTE PCT_1N/D10.1% = PCT_1;
     COMPUTE PCT_2/D10.1% = RETAIL_PRICE / UNIT_SOLD * 100;
BY STORE_CODE
BY CITY

ON STORE_CODE RECOMPUTE AS '*TOTAL'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE RECOMPUTE AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN

When you are posting sample and/or code, use the code tag

</>

to enclose it.

Martin:

Thanks for the reminder about the code tag. I had my code enclosed in the tag, but in my haste to post I deleted the tags. My apologies.

As to my question, I want to make sure that your solution will allow me to calculate percentages of change for all rows including the total row, but also calculate column totals for all of the dollar amounts as in my example at the top. So far, I'm missing something. I moved the one calculation to the second table as per post and I see that the second table in your example has two calculations. It appears that one is calculating the percentage for the rows and the second one for the total row. Is this correct? Do I have a good understanding?

I left the two COMPUTE in the second TABLE FILE just to show you the difference from doing your way and my way.

The "real" result should be something such as (don't pay attention to calculation, they doesn't make sense, just for sample)

TABLE FILE SALES
SUM  COMPUTE UNIT/D5 = UNIT_SOLD / 2;
     COMPUTE PRICE/D8 = RETAIL_PRICE * 3;
-*     COMPUTE PCT_1/D10.1% = PRICE / UNIT * 100;
BY STORE_CODE
BY CITY
ON TABLE HOLD AS ACCOUNTS
END
-RUN

TABLE FILE ACCOUNTS
SUM  UNIT
     PRICE
-*     COMPUTE PCT_1/D10.1% = PCT_1;
     COMPUTE PCT_2/D10.1% = PRICE / UNIT * 100;
BY STORE_CODE
BY CITY

ON STORE_CODE RECOMPUTE AS '*TOTAL'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE RECOMPUTE AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN

Thank you, Martin, for your help.

I understand the computes now. My calculations are correct in my fex.

I equated your last example in my mind to a SAS data step, and that's when I finally understood the code.

I might have one last question for you tomorrow, but I might finally have this fex wrapped up. Thanks again!

Martin Y:

Thanks again for your assistance. My calculation is correct; however, the calculation now appears for all years instead of just the last one. What's my best option to hide the percentage of change for every year except the last one?

I don't know your code but my guess is to have a condition such as :

TABLE FILE abc
SUM
...
   COMPUTE DISP_PCT/P8.2C% MISSING ON = IF DISP_YR EQ LAST_YR THEN PCT ELSE MISSING;
BY ...
...
END

This could be one of the available options.

Martin:

Below is my code. Right now it displays the lag percentage for every year. I want it to be like more like this:

Sales Year
2011 2012 2013 2014 2015 2016 %of Change (Five-Year Diff)
Milk 25 30 35 40 45 50 100%
Bananas 10 12 14 15 16 16 60%

Total 35 42 49 55 61 66 89%

Thanks to your help I have the five-year percentage of change, but it displays for every year. I still want the totals, but I only want one percentage of change column displaying.


SALES.SALES.CATEGORY_NO
SALES.SALES.CAT_TOTAL/P11M
COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST CAT_TOTAL ELSE 0 ;
COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
BY LOWEST SALES.SALES.REGION_NO NOPRINT
BY LOWEST SALES.SALES.REGION_NAME
BY LOWEST SALES.SALES.STORE_NAME
BY LOWEST SALES.SALES.CATEGORY
BY LOWEST SALES.SALES.SALES_YEAR
WHERE ( SALES.SALES.FINCAT_NAME EQ 'All Categories by Sales' );
WHERE ( SALES.SALES.FISCAL_YEAR GE '2010' );
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(WHERE ( SALES.SALES.STORE_NAME EQ '&STORE_NAME.(FIND SALES.SALES.STORE_NAME IN SALES).Store:.' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SALES
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

TABLE FILE SALES
SUM
SALES.SALES.CAT_TOTAL/P11M AS ' '
COMPUTE PCT_2/D10.1% = IF LST.SALES.SALES.LAG_5 NE 0 THEN ( LST.SALES.SALES.CAT_TOTAL - LST.SALES.SALES.LAG_5 ) / LST.SALES.SALES.LAG_5 * 100 ELSE 0; AS 'Five Year Diff'
BY SALES.SALES.STORE_NAME AS 'Store'
BY SALES.SALES.CATEGORY_NO NOPRINT
BY SALES.SALES.CATEGORY AS 'Category'
ACROSS SALES.SALES.SALES_YEAR AS 'Sales Year'

ON SALES.SALES.STORE_NAME RECOMPUTE AS '*TOTAL'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN>

WebFocus 8.2.04
WebFocus 8.2.04

This could be one way, but may not be the easiest one. And you may have to adapt to your situation.

SET ASNAMES = ON

-* EXTRACT SOURCE DATA
DEFINE FILE SALHIST
YR /YY = EFFECTDATE;
END
TABLE FILE SALHIST
SUM OLDSALARY
BY PIN
BY YR
WHERE PIN IN ('000000010', '000000050', '000000060');
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM OLDSALARY
    COMPUTE PCTINCREASE/P6.2C MISSING ON = IF PIN EQ LAST PIN AND YR EQ &YRMAX THEN (OLDSALARY - LAST OLDSALARY) / LAST OLDSALARY * 100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY PIN
BY YR
ON TABLE HOLD AS DETDATA FORMAT FOCUS
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM OLDSALARY
    COMPUTE PCTINCREASE/P6.2C MISSING ON = IF PIN EQ LAST PIN AND YR EQ &YRMAX THEN (OLDSALARY - LAST OLDSALARY) / LAST OLDSALARY * 100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE PIN  /A9 = 'Total';
BY YR
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

-* MERGE DETAIL WITH TOTAL
TABLE FILE DETDATA
SUM OLDSALARY
    PCTINCREASE
BY ROWID
BY PIN
BY YR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0
DEFINE FILE RPTDATA
COL1 /D12.2C = IF YR EQ &YR1.EVAL THEN OLDSALARY   ELSE 0;
COL2 /D12.2C = IF YR EQ &YR2.EVAL THEN OLDSALARY   ELSE 0;
COL3 /D12.2C = IF YR EQ &YR3.EVAL THEN OLDSALARY   ELSE 0;
COL4 /P6.2C  = IF YR EQ &YR3.EVAL THEN PCTINCREASE ELSE 0;
END
TABLE FILE RPTDATA
SUM COL1 AS '&YR1.EVAL'
    COL2 AS '&YR2.EVAL'
    COL3 AS '&YR3.EVAL'
    COL4 AS 'Pct Increase'
BY ROWID NOPRINT
BY PIN
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

Thanks to MartinY's assistance, I have made major progress on my procedure as seen below.

I have one major requirement left, and then it's just formatting/styling, etc.

I called this post Rolling Five Year ... for a reason. My users need to be able to select a sales year and a store. Once the two selections are made, then my user should see six years of data reading from right to left and starting with the selected year and going back five previous years. However, the procedure should only show the one five year percentage of change.

So, if my users selected 2014, the data shown should be 2009 to 2014. If my users selected 2000, then the data shown should be 1995-2000. I'm still researching the forum for ideas, but I'd appreciate any and all suggestions if anyone out there has any. Thanks so much! And thanks again, Martin. I appreciate your wonderful assistance.

SET ASNAMES = ON
SET NODATA = ' '

-SET &RPT_HEAD = &STORE_NAME

-* EXTRACT SOURCE DATA
DEFINE FILE SALES
SALESSET/I4 = EDIT(SALES_YEAR);
YR /YY = FISCSET;
CATEGORY_NO/A6 = EDIT(CATEGORY_NO);
END

TABLE FILE SALES
SUM
SALES.SALES.CATEGORY_NO
SALES.SALES.CATEGORY_TOTAL/P11M
BY SALES.SALES.REGION_NO
BY SALES.SALES.REGION_NAME
BY SALES.SALES.STORE_NAME
BY SALES.SALES.CATEGORY_NAME
BY SALES.SALES.YR
WHERE ( SALES.SALES.FIN_NAME EQ 'All Sales by Category' );
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(<Southwest, Southwest>).Sector:.' );
WHERE ( SALES.SALES.STORE_NAME EQ '&STORE_NAME.(FIND SALES.SALES.STORE_NAME IN SALES).Store:.' );
WHERE ( SALES.SALES.SALES_YEAR GE '2010' );
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
CATEGORY_TOTAL/P11M
	COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST CATEGORY_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
	COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
	COMPUTE PCTCHG/P6.1C%  MISSING ON = IF LST.LAG_5 NE 0 AND YR EQ &YRMAX THEN (CATEGORY_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY CATEGORY_NO/A6
BY CATEGORY_NAME/A28
BY YR
ON TABLE HOLD AS DETDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM
CATEGORY_TOTAL/P11M
	COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST CATEGORY_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
    COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
	COMPUTE PCTCHG/P6.1C%  MISSING ON = IF LST.LAG_5 NE 0 AND YR EQ &YRMAX THEN (CATEGORY_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE CATEGORY_NO/A6 = ' ';
BY TOTAL COMPUTE CATEGORY_NAME/A28 = 'Total Sales';
BY YR
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* MERGE DETAIL WITH TOTAL
TABLE FILE DETDATA
SUM
   	CATEGORY_TOTAL
	PCTCHG
BY ROWID
BY CATEGORY_NO
BY CATEGORY_NAME
BY YR
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0
DEFINE FILE RPTDATA
COL1 /P11M = IF YR EQ &YR1.EVAL THEN CATEGORY_TOTAL ELSE 0;
COL2 /P11M = IF YR EQ &YR2.EVAL THEN CATEGORY_TOTAL ELSE 0;
COL3 /P11M = IF YR EQ &YR3.EVAL THEN CATEGORY_TOTAL ELSE 0;
COL4 /P11M = IF YR EQ &YR4.EVAL THEN CATEGORY_TOTAL ELSE 0;
COL5 /P11M = IF YR EQ &YR5.EVAL THEN CATEGORY_TOTAL ELSE 0;
COL6 /P11M = IF YR EQ &YR6.EVAL THEN CATEGORY_TOTAL ELSE 0;
COL7 /P6.1C% MISSING ON  = IF YR EQ &YR6.EVAL THEN PCTCHG ELSE MISSING;
END
TABLE FILE RPTDATA
SUM COL1 AS '&YR1.EVAL'
  	COL2 AS '&YR2.EVAL'
  	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
  	COL7 AS '5 Year Chg'
BY ROWID NOPRINT
BY CATEGORY_NO NOPRINT
BY CATEGORY_NAME AS 'Category'
HEADING
"Any Corporation"
"All Sales by Category"
"&RPT_HEAD"
FOOTING
"Source: Sales records from Sales Years 2010-2015         "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
END
-RUN
  

BTW 2009 - 2014 it's a 6 years rolling not 5.

Something such as this :

-SET &MAXYR = &USER_SELECT_YR;
-SET &MINYR = &MAXYR - 5;

TABLE FILE SALES
SUM
SALES.SALES.CATEGORY_NO
SALES.SALES.CATEGORY_TOTAL/P11M
BY SALES.SALES.REGION_NO
BY SALES.SALES.REGION_NAME
BY SALES.SALES.STORE_NAME
BY SALES.SALES.CATEGORY_NAME
BY SALES.SALES.YR
WHERE ( SALES.SALES.FIN_NAME EQ 'All Sales by Category' );
WHERE ( SALES.SALES.REGION_NAME EQ '®ION_NAME.(<Southwest, Southwest>).Sector:.' );
WHERE ( SALES.SALES.STORE_NAME EQ '&STORE_NAME.(FIND SALES.SALES.STORE_NAME IN SALES).Store:.' );

WHERE SALES.SALES.SALES_YEAR GE &MINYR;
WHERE SALES.SALES.SALES_YEAR LE &MAXYR;

ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

...

MartinY:

Thank you for your patient assistance. It appears I finally have it, and I have learned much from you.

As to the six v. five-year percentage, we call it a five-year percentage. We use six years of data. Here's how:

Year 1 to Year 2 - Year Differential 1
Year 2 to Year 3 -Year Differential 2
Year 3 to Year 4 - Year Differential 3
Year 4 to Year 5 - Year Differential 4
Year 5 to Year 6 - Year Differential 5

Thanks again. It would seem I have it. I'm going to do a little more testing today, and then if all goes well, I'll mark this as closed tomorrow.

MartinY:

I solved all but one additional element that I discovered while testing. This method works as long as you have six years of data.

Once you have fewer than six years of data, then I need to make some adjustments. I think it's a matter of adding some additional if-then statements, utilizing the dialogue manager and/or changing some of the default values. Additionally, I might need to check in my users and find out what they want to see in terms of display for those instances.

Overall, I think the solution works beautifully though! Thanks again.

Julie,

Pay attention that you need a semicolon after a -SET

-SET &RPT_HEAD = &STORE_NAME;

To make the code more flexible you can do something such as this by adding a trend variable (number of year to compare, in my sample I've used Month)

-DEFAULTH &USER_SELECT_MTH = 12
-DEFAULTH &USER_TREND_MTH  = 5
-SET &MAXMTH = &USER_SELECT_MTH;
-SET &MINMTH = &MAXMTH - &USER_TREND_MTH;
-TYPE MAXMTH: &MAXMTH, MINMTH: &MINMTH

SET ASNAMES = ON
SET NODATA  = ' '

-* EXTRACT SOURCE DATA
DEFINE FILE GGSALES
YRMTH /YYM = DATE;
MTH   /I2  = EDIT(EDIT(FPRINT(YRMTH, 'YYM', 'A7'), '$$$$$99'));
END

TABLE FILE GGSALES
SUM DOLLARS/P10C
BY CATEGORY
BY CITY
BY MTH
WHERE MTH GE '&MINMTH';
WHERE MTH LE '&MAXMTH';
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM MONTH IN A VARIABLE
-DEFAULTH &MTHMAX = 0
TABLE FILE EXTDATA
SUM MAX.MTH AS 'MTHMAX'
ON TABLE HOLD AS MAXMTH FORMAT BINARY
END
-RUN
-READFILE MAXMTH
-SET &MTHMAX = &MTHMAX;
-TYPE MTHMAX: &MTHMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTMTH = 0
TABLE FILE EXTDATA
BY MTH AS 'EXTMTH'
ON TABLE HOLD AS MTHLIST FORMAT BINARY
END
-RUN

-SET &NBMTH   = TRIMV('B', LJUST(11, &LINES, 'A11V'), 11, ' ', 1, 'A11V');
-SET &NBMTH_1 = &NBMTH - 1;
-TYPE &NBMTH, &NBMTH_1

-* VERIFY THAT AT LEAST 2 MOTHS HAVE BEEN SELECTED TO PERFORM REPORT
-IF &NBMTH LE 1 THEN GOTO XEND;

-REPEAT READMTH FOR &I FROM 1 TO &NBMTH STEP 1
-DEFAULTH &MTH.&I = 0
-READFILE MTHLIST
-SET &MTH.&I = &EXTMTH;
-TYPE &MTH.&I
-READMTH

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM DOLLARS
	COMPUTE LAG_1 /P10C = IF CITY EQ LAST CITY THEN LAST DOLLARS ELSE 0 ;

-REPEAT CMPDET FOR &Y FROM 2 TO &NBMTH_1 STEP 1
-SET &Z = &Y - 1;
	COMPUTE LAG_&Y.EVAL/P10C = IF CITY EQ LAST CITY THEN LAST LAG_&Z.EVAL ELSE 0;
-CMPDET

	COMPUTE PCTCHG /P6.1C% MISSING ON = IF LST.LAG_&NBMTH_1.EVAL NE 0 AND LST.MTH EQ &MAXMTH.EVAL THEN (DOLLARS - LAG_&NBMTH_1.EVAL) / LAG_&NBMTH_1.EVAL * 100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY CATEGORY
BY MTH
ON TABLE HOLD AS DETDATA FORMAT FOCUS
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM DOLLARS
	COMPUTE LAG_1 /P10C = IF CITY EQ LAST CITY THEN LAST DOLLARS ELSE 0 ;

-REPEAT CMPTOT FOR &Y FROM 2 TO &NBMTH_1 STEP 1
-SET &Z = &Y - 1;
	COMPUTE LAG_&Y.EVAL/P10C = IF CITY EQ LAST CITY THEN LAST LAG_&Z.EVAL ELSE 0;
-CMPTOT

	COMPUTE PCTCHG /P6.1C% MISSING ON = IF LST.LAG_&NBMTH_1.EVAL NE 0 AND LST.MTH EQ &MAXMTH.EVAL THEN (DOLLARS - LAG_&NBMTH_1.EVAL) / LAG_&NBMTH_1.EVAL * 100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE CATEGORY/A11 = 'Total';
BY MTH
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

-* MERGE DETAIL WITH TOTAL
TABLE FILE DETDATA
SUM	DOLLARS
	PCTCHG
BY ROWID
BY CATEGORY
BY MTH
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-REPEAT INITMTH FOR &I FROM 1 TO &NBMTH STEP 1
-DEFAULTH &MTH&I.EVAL = 0
-INITMTH
DEFINE FILE RPTDATA

-REPEAT DEFDSP FOR &Y FROM 1 TO &NBMTH STEP 1
-SET &MTHDSP = '&' || 'MTH' || &Y.EVAL;
COL&Y.EVAL /P11M = IF MTH EQ &MTHDSP.EVAL THEN DOLLARS ELSE 0;
-DEFDSP

COL&Y.EVAL /P6.1C% MISSING ON  = IF MTH EQ &MAXMTH.EVAL THEN PCTCHG ELSE MISSING;
END
TABLE FILE RPTDATA
SUM
-REPEAT DSPCOL FOR &Y FROM 1 TO &NBMTH STEP 1
-SET &MTHDSP = '&' || 'MTH' || &Y.EVAL;
    COL&Y.EVAL AS '&MTHDSP.EVAL'
-DSPCOL

  COL&Y AS '&NBMTH.EVAL Year Chg'
BY ROWID NOPRINT
BY CATEGORY AS 'Category'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
END
-RUN


-XEND

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

Martin:

Thank you again for your assistance. I am having a brain freeze today in that I know what I need to do, what I would like in terms of results, but I am not having luck in terms of executing the necessary steps.

For the code we worked on June 20-26 (NOT the latest code), I need to change the compute from a rolling percentage of change to a rolling percentage of total for the maximum year displayed.

In other words, if 2013 is selected by the user, the data shown will be 2008-2013 and I need to calculate the percentage of total by category for 2013 only.

I have already removed all my lag calculations. I know I need to use PCT to calculate the row percentages to 2013 column percentage. What am I missing? As I said, I'm having a total brain freeze today.

quote:

I need to calculate the percentage of total by category for 2013 only

What is "percentage of total for 2013 only" ?

The value total of 2013 divided by the total of the previous 5 years (2008-2012) * 100 ?

Please provide sample and/or code.

Trick : doing "pseudo-code" could help to figure a solution to our problem. But since programming is not anymore teached (using GUI is not programming ), it is possible that you don't know "pseudo-coding".

Martin:

Thanks for the quick reply. Yes, you are correct. I should have included my code and/or a sample. I hurriedly asked my question and then started researching other things on this forum to help with different challenges. That was my error.

Here's a sample or idea of what I am trying to achieve:

2008 2009 2010 2011 2012 2013 % of Total for FY 2013
Apple 100 113 101 125 132 155 26.6%
Banana 111 101 95 129 144 165 28.3%
Mango 57 43 29 8 7 5 0.9%
Orange 62 79 99 137 203 253 43.4%
Papaya 9 3 1 0 4 1 0.2%
Star 12 2 1 0 5 4 0.7%

Total 351 341 326 399 495 583 100.0%

If I switch my selection to be 2014, then my data will start in 2009 and end in 2014. I'll need to calculate the percentage of total for FY 2014, and so forth.

I get that I need to use either the PCT or the COMPUTE function. I think the COMPUTE function would be best for two reasons: (1) if my users want group subtotals, it will be easier to do that with a compute as in my code below and (2) a compute should be more precise with the decimal place after the decimal point. The PCT, no matter how hard I try, results in 27% v. 26.6%, etc. Another post alerted me to trying a compute instead of PCT.

Here's my code, and I've highlighted the parts I think need revision. Again, for some reason, my brain has frozen and I am unable to make any additional progress.

SET ASNAMES = ON
SET NODATA = 'NA'

-SET &RPT_HEAD = &STORE_NAME

-* EXTRACT SOURCE DATA
DEFINE FILE EXPENSES
FISCSET/I4 = EDIT(FISCAL_YEAR);
YR /YY = FISCSET;
GROUP_NO/A6 = EDIT(GROUP_NO);
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR - 5;

TABLE FILE EXPENSES
SUM
EXPENSES.EXPENSES.GROUP_NO
EXPENSES.EXPENSES.GROUP_TOTAL/P11M
BY EXPENSES.EXPENSES.REGION
BY EXPENSES.EXPENSES.REGION_NAME
BY EXPENSES.EXPENSES.STORE_NAME
BY EXPENSES.EXPENSES.GROUP_TYPE
BY EXPENSES.EXPENSES.GROUP_TYPE_NAME
BY EXPENSES.EXPENSES.GROUP_NAME
BY EXPENSES.EXPENSES.YR
WHERE ( EXPENSES.EXPENSES.FIN_NAME EQ 'All Operating Expenses' );
WHERE ( EXPENSES.EXPENSES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE ( EXPENSES.EXPENSES.STORE_NAME EQ '&STORE_NAME.(FIND EXPENSES.EXPENSES.STORE_NAME IN EXPENSES).Store:.' );
WHERE ( EXPENSES.EXPENSES.YR GE &MINYR);
WHERE ( EXPENSES.EXPENSES.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/P11M
*-I believe these lines are no longer necessary-*
	COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
	COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
*-This is a line to update for the percent of total-*	
	COMPUTE PCTCHG/P8.1C%  MISSING ON = IF LAG_5 NE 0 AND YR EQ &YRMAX THEN (GROUP_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY GROUP_TYPE/P3
BY GROUP_TYPE_NAME/A20
BY GROUP_NO/A6
BY GROUP_NAME/A30
BY YR
ON TABLE HOLD AS DETDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR SUBTOTAL ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/P11M
*-I believe these lines are no longer necessary-*
	COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
    COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
*-This is a line to update for the percent of total-*	
	COMPUTE PCTCHG/P8.1C%  MISSING ON = IF LAG_5 NE 0 AND YR EQ &YRMAX THEN (GROUP_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 2;
BY GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A20 = ' ';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = 'Group Subtotal';
BY YR
ON TABLE HOLD AS STOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/P11M
*-I believe these lines are no longer necessary-*
	COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
    COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
*-This is a line to update for the percent of total-*	
	COMPUTE PCTCHG/P8.1C%  MISSING ON = IF LAG_5 NE 0 AND YR EQ &YRMAX THEN (GROUP_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A20 = 'Total Operating Exp.';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = ' ';
BY YR
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* MERGE DETAIL WITH TOTAL AND SUBTOTALS
TABLE FILE DETDATA
SUM
   	GROUP_TOTAL
	PCTCHG
BY LOWEST GROUP_TYPE
BY LOWEST GROUP_TYPE_NAME
BY LOWEST GROUP_NO
BY LOWEST GROUP_NAME
BY LOWEST ROWID
BY GROUP_TYPE_NAME
BY GROUP_NO
BY GROUP_NAME
BY YR
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE STOTDATA
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0
DEFINE FILE RPTDATA
COL1 /P11M = IF YR EQ &YR1.EVAL THEN GROUP_TOTAL ELSE 0;
COL2 /P11M = IF YR EQ &YR2.EVAL THEN GROUP_TOTAL ELSE 0;
COL3 /P11M = IF YR EQ &YR3.EVAL THEN GROUP_TOTAL ELSE 0;
COL4 /P11M = IF YR EQ &YR4.EVAL THEN GROUP_TOTAL ELSE 0;
COL5 /P11M = IF YR EQ &YR5.EVAL THEN GROUP_TOTAL ELSE 0;
COL6 /P11M = IF YR EQ &YR6.EVAL THEN GROUP_TOTAL ELSE 0;
/*The line below will obviously be updated*/
COL7 /P8.1C% MISSING ON  = IF YR EQ &YR6.EVAL THEN PCTCHG ELSE MISSING;
END

TABLE FILE RPTDATA
SUM COL1 AS '&YR1.EVAL'
  	COL2 AS '&YR2.EVAL'
  	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
/*The line below will obviously be updated*/
  	COL7 AS '5 Year Chg'
BY LOWEST GROUP_TYPE NOPRINT
BY LOWEST ROWID NOPRINT
BY GROUP_TYPE_NAME AS 'Subdivision'
BY GROUP_NO NOPRINT
BY GROUP_NAME AS 'Category'

HEADING
"Operating Expenses by Category"
"&RPT_HEAD"
"Fiscal Years &MINYR - &MAXYR"
FOOTING
"Source: Operating expense summaries supplied by the plants themselves        "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
ENDSTYLE
END
-RUN

quote:

-SET &RPT_HEAD = &STORE_NAME

Julie, always put a semicolon after a -SET command.
Also, even when posting sample data as you did, use the code tag to enclose it. That way it will stay aligned and more readable.

As for your calculation issue.

One technique could be to use the following :

TABLE FILE CAR
-* THIS IS C1
SUM RETAIL_COST AS 'TOTAL COUNTRY RCOST'
BY COUNTRY

-* THIS IS C2
SUM RETAIL_COST AS 'TOTAL CAR COST'
-* COMPUTE BASED ON PREVIOUS 'Cx' COLUMNS
    COMPUTE CST_PCT /P6.2C = C2 / C1 * 100;
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL RECOMPUTE
ON TABLE COLUMN-TOTAL RECOMPUTE
END
-RUN

Another one is to :
1- Hold your data at the detailed level that you need (in your case per category. Do not create totals).
2- Create the total per year (351, 341, 326, 399, 495, 583) and Hold it.
3- Join the total per year in step -2- to detailed rows in step -1- based on year. So you will have same total column added to each same year rows and hold in a new file.
4- From step -3- hold file, perform your compute with det_col (step -1- ) / tot_col (step -3-) * 100.

Thank you, Martin.

You have read my mind about what I am trying to accomplish and the possible techniques.

I used the code tag,

. Did it go away? I put my code in between the two CODE mentions. And thanks for the reminder about the -SET command. I am going to add that, but obviously, it did not click in my brain. Thank you so much!

Your code is between the code tag. Use it also for data sample.

Thank you, Martin, for letting me know that trick about posting sample data. I learned something else from you, and I am grateful for that.

I now need to ask a different question about my code posted on Aug. 22 at 11:56 a.m.

Using your example (thank you again) I designed the code with the understanding I would use it in 8.1.05M. I've now had to return to 8.0.09.

The fex itself does not produce an error; however, when I place the code in an html file, I get two errors, one is a FOC224 which I read is "serious enough to halt interpretation" and the other is "unable to parse MASTERFILE RPTDATA."

read in other posts about changing the timeout connection (I had already set mine to 6000 in DevStudio instead of the default 60), and I've read about adding this line SET XRETRIEVE = OFF.

Any thoughts?

quote:

SET XRETRIEVE = OFF

The above will avoid data to be retrieved from your DB meaning that depending where is placed in your code, it is possible that RPTDATA does not exist.
If the code posted on Aug. 22 at 11:56 a.m. is executed in one run it shouldn't give you any error.

BUT,

quote:

"unable to parse MASTERFILE RPTDATA."

will be displayed if you try to open the fex with AS from the fex step : « -* PRODUCE FINAL REPORT » since RPTDATA is only hold in memory at execution. So AS cannot know it.

Furthermore it's difficult to determine what cause « FOC224 which I read is "serious enough to halt interpretation" ».
You may need to contact IB Support.

I thought this was solved. I had put this on the shelf for awhile, and now that I have returned to it, I have found one final challenge with my code.

I think it has to be in the counter and the way the grand total section works or how the fex is handling missing values. At this time, I do not think it is a data issue as it occurs in other fexes relying upon different data sets. I've read up on missing values quite a bit, and I'm not sure if the answer might be to use MISSING-TEST. I've already set the MAS file to have missing on.

What happens is this:

I have data from 2016 back to 2006. When I place this code in an HTML iframe and add the All Stores selection in the parameter, the code duplicates the total rows BUT only for a certain range of years 2007-2012. If I select 2013 or forward to 2016, I'm fine. If I select 2006, I'm fine.

I've included a sample of what happens. The field names are different than what's in the code below, but the sample should provide a brief illustration of the issue much better than I can explain in words.

Crafts	Supplies	$637,783,833 	$666,623,450 	$656,586,391 	$630,568,607 	$640,758,211 	$685,570,386 	7.50%
	Studio space	$163,238,579 	$164,013,354 	$161,924,114 	$183,904,230 	$185,243,163 	$199,517,950 	22.20%
	Instructors	$74,554,019 	$91,896,991 	$94,875,932 	$100,933,187 	$101,682,884 	$107,126,565 	43.70%
	Program Subtotal	$875,578,438 	$922,535,803 	$913,388,446 	$915,408,034 	$927,686,269 	$992,216,913 	13.32%
Sports	Equipment	$139,466,166 	$149,479,798 	$150,689,042 	$147,234,170 	$149,060,901 	$161,985,703 	16.10%
	Venue space	$262,501,657 	$277,677,160 	$327,280,075 	$337,022,698 	$350,051,176 	$371,679,162 	41.60%
	Coaches	$103,064,200 	$109,559,749 	$108,397,643 	$114,675,836 	$111,232,798 	$118,535,493 	15.00%
	Program Subtotal	$505,032,023 	$536,716,707 	$586,366,760 	$598,932,704 	$610,344,875 	$652,200,358 	29.14%
Building	Maintenance	$103,421,716 	$113,658,762 	$113,961,147 	$119,014,572 	$123,541,297 	$141,283,300 	36.60%
	Program Subtotal	$103,421,716 	$113,658,762 	$113,961,147 	$119,014,572 	$123,541,297 	$141,283,300 	36.60%
Other	Repairs	-	-	$115,786,472 	$267,433,467 	$124,308,863 	$132,133,582 	-
	Program Subtotal	-	-	$115,786,472 	$267,433,467 	$124,308,863 	$132,133,582 	-
Total General Use Exp.		$1,484,032,177 	-	-	-	-	-	-
Total General Use Exp.		-	$1,572,911,272 	$1,613,716,353 	$1,633,355,310 	$1,661,572,441 	$1,785,700,571 	20.33%
  

-SET &RPT_HEAD = IF &STORE_NAME EQ '_FOC_NULL' THEN 'All Stores' ELSE &STORE_NAME;

-* EXTRACT SOURCE DATA
DEFINE FILE EXPENSES
FISCSET/I4 = EDIT(FISCAL_YEAR);
YR /YY = FISCSET;
GROUP_NO/A6 = EDIT(GROUP_NO);
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR - 5;

TABLE FILE EXPENSES
SUM
EXPENSES.EXPENSES.GROUP_NO
EXPENSES.EXPENSES.GROUP_TOTAL
BY EXPENSES.EXPENSES.REGION
BY EXPENSES.EXPENSES.REGION_NAME
BY EXPENSES.EXPENSES.STORE_NAME
BY EXPENSES.EXPENSES.GROUP_TYPE
BY EXPENSES.EXPENSES.GROUP_TYPE_NAME
BY EXPENSES.EXPENSES.GROUP_NAME
BY EXPENSES.EXPENSES.YR
WHERE ( EXPENSES.EXPENSES.FIN_NAME EQ 'All Expenses );
WHERE ( EXPENSES.EXPENSES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE ( EXPENSES.EXPENSES.STORE_NAME EQ '&STORE_NAME.(FIND EXPENSES.EXPENSES.STORE_NAME IN EXPENSES).Store:.' );
WHERE ( EXPENSES.EXPENSES.YR GE &MINYR);
WHERE ( EXPENSES.EXPENSES.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

-*CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/D20
	COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
        COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
	COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
        COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
        COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
	COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY GROUP_TYPE/P3
BY GROUP_TYPE_NAME/A28
BY GROUP_NO/A6
BY GROUP_NAME/A30
BY YR
ON TABLE HOLD AS DETDATA FORMAT FOCUS
END
-RUN

-* CALCULATE INCREASE PCT FOR SUBTOTAL ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/D20
	COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
    COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
	COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
    COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
    COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
	COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 2;
BY GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A28 = ' ';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = 'Group Subtotal';
BY YR
ON TABLE HOLD AS STOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/D20
	COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
    COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
	COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
    COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
    COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
	COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A28 = 'Total Expenses';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = ' ';
BY YR
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

-* MERGE DETAIL WITH TOTAL AND SUBTOTALS
TABLE FILE DETDATA
SUM
   	GROUP_TOTAL
	PCTCHG
BY LOWEST GROUP_TYPE
BY LOWEST GROUP_TYPE_NAME
BY LOWEST GROUP_NO
BY LOWEST GROUP_NAME
BY LOWEST ROWID
BY GROUP_TYPE_NAME
BY GROUP_NO
BY GROUP_NAME
BY YR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE STOTDATA
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0
DEFINE FILE RPTDATA
COL1 /P11M  MISSING ON = IF YR EQ &YR1.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL2 /P11M  MISSING ON = IF YR EQ &YR2.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL3 /P11M  MISSING ON = IF YR EQ &YR3.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL4 /P11M  MISSING ON = IF YR EQ &YR4.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL5 /P11M  MISSING ON = IF YR EQ &YR5.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL6 /P11M  MISSING ON = IF YR EQ &YR6.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL7 /P6.1C% MISSING ON = IF YR EQ &YR6.EVAL THEN PCTCHG ELSE MISSING;
END

TABLE FILE RPTDATA
SUM COL1 AS '&YR1.EVAL'
  	COL2 AS '&YR2.EVAL'
  	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
  	COL7 AS '5 Year Chg'
BY LOWEST CAT_TYPE NOPRINT
BY LOWEST ROWID NOPRINT
BY CAT_TYPE_NAME AS 'Program'
BY R_CAT_NBR NOPRINT
BY CAT_NAME AS 'Category'

HEADING
"Operating Expenditures by Program"
"&RPT_HEAD"
"Fiscal Years &MINYR - &MAXYR"
FOOTING
"Source: Budget summaries"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
ENDSTYLE
END
-RUN
  

Julie,

First thing that I can think of is that you may have a different code value as for "General Use Exp." description.
What I mean is that you may have somewhere between 2007-2012 :

TypeCode TypeDescription
10       General Use Exp.
11       General Use Exp.

But it's difficult to say without your real data code & value.

Also, I have difficulties to understand how you can do the following :

BY LOWEST CAT_TYPE NOPRINT
BY LOWEST ROWID NOPRINT
BY CAT_TYPE_NAME AS 'Program'
BY R_CAT_NBR NOPRINT
BY CAT_NAME AS 'Category'

When in your hold file RPTDATA the hold fields are (the above doesn't exist in the hold file) :

BY LOWEST GROUP_TYPE
BY LOWEST GROUP_TYPE_NAME
BY LOWEST GROUP_NO
BY LOWEST GROUP_NAME
BY LOWEST ROWID
BY GROUP_TYPE_NAME
BY GROUP_NO
BY GROUP_NAME
BY YR

And finally, since it seems to be an issue with the total row, I will pay attention to that step and look at the resulting data before going any further.

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM GROUP_TOTAL/D20
    COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
    COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
    COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
    COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
    COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
    COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A28 = 'Total Expenses';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = ' ';
BY YR
-*ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

It is difficult to figure where it could be since your sample result don't even have same displayed data as what your code is generating.
Where are "Total Expenses" and "Group Subtotal" in your sample ?

I understand that you may not want to show real confidential data, but show your sample as it really should be according to your code.

Thanks, Martin, for your observations. I will repost everything here in a little while. My apologies. I posted in a hurry yesterday, and I missed some items. Thanks for your attention to detail.

Details makes perfect

Also, since you are re-posting into the same original post, I suggest that you change back your post status to remove the [SOLVED] until it is.

Martin,

Thank you again for your quick replies. I have reposted the code, and I have also tried to create an example using data that more closely matches the code. See below. I will also change the SOLVED here in a minute. Again, my apologies. I posted this in a hurry on my way out of the door last night. In retrospect, I should have just waited until today to begin the post.

SET &RPT_HEAD = IF &STORE_NAME EQ '_FOC_NULL' THEN 'All Stores' ELSE &STORE_NAME;

-* EXTRACT SOURCE DATA
DEFINE FILE EXPENSES
FISCSET/I4 = EDIT(FISCAL_YEAR);
YR /YY = FISCSET;
GROUP_NO/A6 = EDIT(GROUP_NO);
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR - 5;

TABLE FILE EXPENSES
SUM
EXPENSES.EXPENSES.GROUP_NO
EXPENSES.EXPENSES.GROUP_TOTAL
BY EXPENSES.EXPENSES.REGION
BY EXPENSES.EXPENSES.REGION_NAME
BY EXPENSES.EXPENSES.STORE_NAME
BY EXPENSES.EXPENSES.GROUP_TYPE
BY EXPENSES.EXPENSES.GROUP_TYPE_NAME
BY EXPENSES.EXPENSES.GROUP_NAME
BY EXPENSES.EXPENSES.YR
WHERE ( EXPENSES.EXPENSES.FINANCIAL_TYPE EQ 'All Expenses );
WHERE ( EXPENSES.EXPENSES.REGION_NAME EQ 'REGION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE ( EXPENSES.EXPENSES.STORE_NAME EQ '&STORE_NAME.(FIND EXPENSES.EXPENSES.STORE_NAME IN EXPENSES).Store:.' );
WHERE ( EXPENSES.EXPENSES.YR GE &MINYR);
WHERE ( EXPENSES.EXPENSES.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

-*CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/D20
	COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
    COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
	COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
    COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
    COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
	COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY GROUP_TYPE/P3
BY GROUP_TYPE_NAME/A28
BY GROUP_NO/A6
BY GROUP_NAME/A30
BY YR
ON TABLE HOLD AS DETDATA FORMAT FOCUS
END
-RUN

-* CALCULATE INCREASE PCT FOR SUBTOTAL ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/D20
	COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
    COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
	COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
    COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
    COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
	COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 2;
BY GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A28 = ' ';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = 'Group Subtotal';
BY YR
ON TABLE HOLD AS STOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/D20
	COMPUTE LAG_1/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE MISSING;
    COMPUTE LAG_2/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE MISSING;
	COMPUTE LAG_3/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE MISSING;
    COMPUTE LAG_4/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE MISSING;
    COMPUTE LAG_5/D20 MISSING ON NEEDS SOME DATA  = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE MISSING;
	COMPUTE PCTCHG/P6.1C% MISSING ON NEEDS SOME DATA = IF LAG_5 IS MISSING AND GROUP_TOTAL IS MISSING AND YR EQ &YRMAX THEN MISSING ELSE (GROUP_TOTAL - LAG_5) / LAG_5 *100 ;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A28 = 'Total Expenses';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = ' ';
BY YR
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

-* MERGE DETAIL WITH TOTAL AND SUBTOTALS
TABLE FILE DETDATA
SUM
   	GROUP_TOTAL
	PCTCHG
BY LOWEST GROUP_TYPE
BY LOWEST GROUP_TYPE_NAME
BY LOWEST GROUP_NO
BY LOWEST GROUP_NAME
BY LOWEST ROWID
BY GROUP_TYPE_NAME
BY GROUP_NO
BY GROUP_NAME
BY YR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE STOTDATA
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0
DEFINE FILE RPTDATA
COL1 /P11M  MISSING ON = IF YR EQ &YR1.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL2 /P11M  MISSING ON = IF YR EQ &YR2.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL3 /P11M  MISSING ON = IF YR EQ &YR3.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL4 /P11M  MISSING ON = IF YR EQ &YR4.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL5 /P11M  MISSING ON = IF YR EQ &YR5.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL6 /P11M  MISSING ON = IF YR EQ &YR6.EVAL THEN GROUP_TOTAL ELSE MISSING;
COL7 /P6.1C% MISSING ON = IF YR EQ &YR6.EVAL THEN PCTCHG ELSE MISSING;
END

TABLE FILE RPTDATA
SUM COL1 AS '&YR1.EVAL'
  	COL2 AS '&YR2.EVAL'
  	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
  	COL7 AS '5 Year Chg'
BY LOWEST GROUP_TYPE NOPRINT
BY LOWEST ROWID NOPRINT
BY GROUP_TYPE_NAME AS 'Group Type'
BY GROUP_NO NOPRINT
BY GROUP_NAME AS 'Group Subtotal'

HEADING
"Operating Expenditures by Group"
"&RPT_HEAD"
"Fiscal Years &MINYR - &MAXYR"
FOOTING
"Source: Budget summaries"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
ENDSTYLE
END
-RUN
  

Here is some sample data:

All Stores - All Expenses by Group and Group Type								
Fiscal Years 2007 - 2012								
								
Group	Group Type	2007	2008	2009	2010	2011	2012	5 Year Chg
Crafts	Stickers	$637 	$634 	$582 	$568 	$640 	$603 	-5.34%
	Acid-free paper	$238 	$413 	$515 	$617 	$717 	$699 	193.70%
	Rubber stamps	$200 	$186 	$199 	$205 	$217 	$265 	32.50%
	Paper trimmers	$972 	$2,015 	$2,500 	$2,606 	$2,667 	$3,067 	215.53%
	Group Subtotal	$2,047 	$3,248 	$3,796 	$3,996 	$4,241 	$4,634 	126.38%
Frames	Custom frames	$1,946 	$1,797 	$2,152 	$2,341 	$2,345 	$2,367 	21.63%
	Custom mats	$6,512 	$7,677 	$7,892 	$7,899 	$7,995 	$7,999 	22.83%
	Box frames	$3,064 	$4,555 	$4,666 	$4,777 	$4,885 	$4,800 	56.66%
	Shadow boxes	$8,062 	$8,547 	$8,798 	$8,799 	$8,801 	$7,999 	-0.78%
	Other display 	$933 	$610 	$500 	$515 	$518 	$399 	-57.23%
	Group Subtotal	$20,517 $23,186 $24,008 $24,331 $24,544 $23,564	14.85%
Floral	Dried flowers	$3,276 	$3,277 	$2,555 	$3,111 	$3,579 	$3,788 	15.63%
	Group Subtotal	$3,276 	$3,277 	$2,555 	$3,111 	$3,579 	$3,788 	15.63%
Art	Paintbrushes	-	-	$115 	$150 	$168 	$179 	0.00%
	Group Subtotal	-	-	$115 	$150 	$168 	$179 	0.00%
Total Expenses		$25,840 	-	-	-	-	-	-
Total Expenses		-	$29,711 $30,474	$31,913 $32,532	$32,165	19.66%
  

I'm back to my original suggestion : look at the data step by step to insure that each step generates what it should the way it should.

Another fast option is to produce your final report but with the NOPRINT fields printed. It may give you a idea of where is the issue on the total. I again suspect that you have a break in the used code (NOPRINT field) that it shouldn't.

Always a good idea to look at the whole data before hiding some (NOPRINT)

Thanks for your reply.

I had previously gone through the code step by step, but I did it again today a few different ways. I have identified the section of code which appears to me is causing the "break."

It actually breaks higher than the NORPINT area.

The conflict seems to be in the section of code which creates the group subtotals. For the group subtotals, the code "creates" a number for the group number. I suspect it might be a record row number. In certain cases (that is, for certain years), the code assigns two numbers of 134 and 167 or something along those lines. Two colleagues of mine thoroughly reviewed the data in the original table, and it does not appear to have any data issues.

At this point, my thought is that it's because of the null value that the code "creates" two separate numbers for the group number. It might require a look at handling missing values.

Thoughts?

Once again :

quote:

I'm back to my original suggestion : look at the data step by step to insure that each step generates what it should the way it should.

Then make adjustment to have them properly grouped as you whish.

I can't help further. If it's related to missing data you need to manage it. your code look good now need to adjust according to possible data issue if no other choices.