Hello all.

I need to calculate the delta between two dynamic values that are really in the same column - year over year.

I developed a work around using an ACROSS - and then placed the COMPUTE after the ACROSS - which gave me the value I wanted in the place I wanted. Try it without the across, and you get a mess.

Trouble is, I cannot style the new DELTA column or add conditional styling - something I need for negative numbers. Nothing I do is recognized (except SQUEEZE width, which is respected).

Here is the code and an example of the output. I would be happy to get rid of the ACROSS if there is some way to calculdate the delta via something like C3(R2-R1) which I tried to no avail.

-DEFAULT &DBS1 = 'SURG'
-DEFAULT &Year = '2010'
-DEFAULT &Month = '09'

-SET &PRYEAR= &Year -1 ;
-SET &PRYEAR2= &Year -2 ;
-SET &PRYEAR3= &Year -3 ;

-INCLUDE inc_varsre.fex

TABLE FILE NDAY_GEAC_SUMMARY
SUM
     TOTAL_GRANT_AMT/D12CM AS ''
BY  &SORTVAR AS 'Grant Spending (YTD)'
ACROSS YEAR AS ''
     COMPUTE
          TOTAL_GRANT_AMT/D12C = ( C3 - C2 ); AS 'Delta'
WHERE YEAR EQ '&Year' OR '&PRYEAR';
WHERE EDIT(YEARMO,'$$$$99')  LE '&Month';
WHERE &WHEREVAR EQ '&MEDDIV';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     LEFTMARGIN=0.000000,
     RIGHTMARGIN=0.000000,
     TOPMARGIN=0.000000,
     BOTTOMMARGIN=0.000000,
     SQUEEZE=ON,
     PAGECOLOR='WHITE',
$
TYPE=REPORT,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     BORDER-TOP-COLOR=RGB(18 72 124),
     BORDER-BOTTOM-COLOR=RGB(18 72 124),
     BORDER-LEFT-COLOR=RGB(18 72 124),
     BORDER-RIGHT-COLOR=RGB(18 72 124),
     FONT='VERDANA',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     COLUMN=N1,
     FONT='CALIBRI',
     JUSTIFY=LEFT,
$
TYPE=TITLE,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     BORDER-TOP-COLOR=RGB(18 72 124),
     BORDER-BOTTOM-COLOR=RGB(18 72 124),
     BORDER-LEFT-COLOR=RGB(18 72 124),
     BORDER-RIGHT-COLOR=RGB(18 72 124),
     FONT='ARIAL',
     SIZE=8,
     COLOR='WHITE',
     BACKCOLOR=RGB(18 72 124),
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N1,
     JUSTIFY=LEFT,
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     FONT='ARIAL',
     SIZE=7,
     COLOR=RGB(18 72 124),
     STYLE=BOLD,
$
TYPE=ACROSSTITLE,
     ACROSS=1,
     FONT='ARIAL',
     SIZE=7,
     COLOR=RGB(18 72 124),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N1,
     SQUEEZE=1.791667,
$
TYPE=REPORT,
     COLUMN=N4,
     SQUEEZE=0.791667,
$
TYPE=REPORT,
     COLUMN=N3,
     SQUEEZE=0.791667,
$
TYPE=REPORT,
     COLUMN=N2,
     SQUEEZE=0.791667,
$
ENDSTYLE
END

And BTW, this is one of many fex that are placed on a DOC COMPOSER page for final output.

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

you can put it first in a hold file, or define the two years values in separate fields

There are already so many variables involved, a define would be tricky in this case...

Really - a HOLD file? There is really no straight-forward way to call a specific (Column.Row - Column.Row) function of some sort?

What about something like this:

-SET &THIS_YEAR = EDIT(&YYMD,'9999');
-SET &LAST_YEAR = &THIS_YEAR - 1;

...

TABLE FILE NDAY_GEAC_SUMMARY
SUM
COMPUTE TOTAL_GRANT_AMT_LY/D12CM = IF YEAR EQ &LAST_YEAR THEN TOTAL_GRANT_AMT ELSE 0; AS 'Grant &LAST_YEAR'
COMPUTE TOTAL_GRANT_AMT_TY/D12CM = IF YEAR EQ &THIS_YEAR THEN TOTAL_GRANT_AMT ELSE 0; AS 'Grant &THIS_YEAR'

COMPUTE TOTAL_GRANT_AMT_DL/D12C = ( TOTAL_GRANT_AMT_TY - TOTAL_GRANT_AMT_LY ); AS 'Delta'

BY  &SORTVAR AS 'Grant Spending (YTD)'
...

Thank you Francis. I get it.

Very helpful, as always!