Challenge: We have a one-year change and a five-year change calculated at the end of each row of data in a table.

As of yesterday, we had eight years' worth of data. Then, we added another year. At that point, I had to manually change the two values near the bottom of the code from N14 to N15.

I don't want to have to make this manual change every time I add another year.

I found two posts that might be helpful to me. (https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7841087331

http://forums.informationbuild...97096316#9997096316).

In each post it seems to me the user is trying to utilize conditional styling based on a specific value. I'm trying to conditionally format the last year of data. I think the principle is similar though.

Here's my code:

SET EXCELSERVURL = ''
SET EMGSRV=OFF
SET MESSAGE = OFF
SET ERROROUT = OFF
SET WARNING = OFF
SET TRMOUT = OFF
SET MSG = OFF
SET HOLDATTR = ON
SET EMPTYREPORT = ON

DEFINE FILE SALES
YEAR/I4=EDIT(SALES.SALES.QUARTER_CALENDAR_YEAR);
END
TABLE FILE SALES
SUM
SALES.SALES.UNITS_SOLD/D10
COMPUTE LAG_1/D10 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST UNITS_SOLD ELSE 0;
COMPUTE LAG_2/D10 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
COMPUTE LAG_3/D10 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
COMPUTE LAG_4/D10 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
COMPUTE LAG_5/D10 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
COMPUTE PERCENT_LAG_1/D10.1% = IF LAG_5 NE 0 THEN ( UNITS_SOLD - LAG_1 ) / LAG_1 * 100 ELSE 0; AS 'One Year Diff'
COMPUTE PERCENT_LAG_5/D10.1% = IF LAG_5 NE 0 THEN ( UNITS_SOLD - LAG_5 ) / LAG_5 * 100 ELSE 0; AS 'Five Year Diff'
BY LOWEST SALES.SALES.REGION
BY SALES.SALES.REGION_NAME
BY LOWEST SALES.SALES.STORE_NAME
BY LOWEST SALES.SALES.YEAR
WHERE ( SALES.SALES.SALES_TERM EQ 'FY' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS EXPORTUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
TABLE FILE EXPORTUS
SUM
LST.EXPORTUS.EXPORTUS.PERCENT_LAG_1 NOPRINT AS 'Lag 1'
LST.EXPORTUS.EXPORTUS.PERCENT_LAG_5 NOPRINT AS 'Lag 5'
BY EXPORTUS.EXPORTUS.REGION NOPRINT
BY EXPORTUS.EXPORTUS.REGION_NAME AS 'Region'
BY EXPORTUS.EXPORTUS.STORE_NAME AS 'Store'
SUM
EXPORTUS.EXPORTUS.UNITS_SOLD/D10 AS ' '
BY EXPORTUS.EXPORTUS.REGION NOPRINT
BY EXPORTUS.EXPORTUS.REGION_NAME AS 'Region'
BY EXPORTUS.EXPORTUS.STORE_NAME AS 'Store'
ACROSS EXPORTUS.EXPORTUS.YEAR AS 'Fiscal Year'
COMPUTE LAG_1/D10.1% = LST.PERCENT_LAG_1; AS '1 Yr Chg'
COMPUTE LAG_5/D10.1% = LST.PERCENT_LAG_5; AS '5 Yr Chg'
ON EXPORTUS.EXPORTUS.REGION_NAME SUBFOOT
" "
ON TABLE SUBHEAD
"Julie's Chain of Stores"
"Units Sold"
"Fiscal Year Totals"
ON TABLE SUBFOOT
" "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT,
HFREEZE=TOP,
$
TYPE=TITLE,
BORDER-BOTTOM=ON,
BORDER-BOTTOM-COLOR=RGB (250 250 250),
$
TYPE=ACROSSVALUE,
JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
ACROSS=2,
WRAP=ON,
WIDTH=3.0,
GRID=ON,
$
TYPE=DATA,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N2,
JUSTIFY=LEFT,
SQUEEZE=OFF,
$
TYPE=DATA,
COLUMN=N3,
JUSTIFY=LEFT,
$
TYPE=DATA,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
$
TYPE=TABFOOTING,
BORDER-TOP=OFF,
$
TYPE=TABFOOTING,
LINE=1,
JUSTIFY=RIGHT,
$
TYPE=TABFOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
STYLE=NORMAL,
$
TYPE=SUBFOOT
BORDER-BOTTOM=ON,
BORDER-TOP=ON,
$
TYPE=SUBFOOT
BORDER-BOTTOM=LIGHT,
BORDER-TOP=LIGHT,
$
TYPE=DATA,
COLUMN=N15,
BORDER-LEFT=LIGHT,
BORDER-LEFT-COLOR=RGB (0 58 99),
$
TYPE=ACROSSVALUE,
COLUMN=N15,
BORDER-LEFT=LIGHT,
BORDER-LEFT-COLOR=RGB (0 58 99),
$
ENDSTYLE
END
-RUN

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

WebFocus 8.2.04
WebFocus 8.2.04

If I understand your requirement correctly:
* Report is going across years.
* Latest year column to be conditionally
formatted.
* The latest year is known only at
runtime.

Run the following example, using the IB sample file, and you should see that only the '2016' column is red.

-*
-* Define temporary fields.  
-*
DEFINE FILE GGSALES
  YEAR/YY = IF PRODUCT EQ 'Mug' THEN 2016 ELSE DATE;
END
-*
-* What is the latest year to be reported?
-*
TABLE FILE GGSALES
SUM MAX.YEAR
ON TABLE SAVE AS SVYEARS
END
-RUN
-READ SVYEARS &MAXYEAR.A4.
-TYPE Max Year: &MAXYEAR
-*
-* Create Sales report.  
-*
TABLE FILE GGSALES
"Sales Across Year by Region"
SUM    DOLLARS/D12
ACROSS YEAR AS ''
BY     REGION
ON TABLE SET STYLE *
 INCLUDE=Business.sty, $
-* The latest year should display in red.  
 TYPE=DATA,        WHEN = YEAR EQ &MAXYEAR, COLOR=RED, $
 TYPE=ACROSSVALUE, WHEN = YEAR EQ &MAXYEAR, COLOR=RED, $
ENDSTYLE
END  

This message has been edited. Last edited by: David Briars,

Thank you, David. The solution is very close, if not exactly, what I need.

To summarize your three points as related to my report.

1. Yes, the report is going across years.
2. Yes, the latest year needs to be conditionally formatted.
3. No. The latest year is known now, but next year when we add another year's worth of data, I don't want to have to remember to manual adjust the formatting on my many similar reports.

I think I might be able to take your solution and tweak it. We shall see. Thanks.

Hi JulieA,

David's solution does exactly what you're requesting even for point -3-.

If you pay attention, the first TABLE file retrieve the latest existing year.
The second TABLE file produce the report using the max found year to be styled.

You need to apply the same technic and you won't have to change your code when new year appears in the report.

In David's sample it just twick the year to have a displayable sample.

Here a more detailed sample with more displayed years. Since no IBI sample file have a lot of years, we have to create some.

DEFINE FILE GGSALES
PRD1ST /A1 = EDIT(PRODUCT, '9');
YEAR   /YY = DECODE PRD1ST ('B' 1996 'C' 1997 'E' 1998 'L' 1999 'M' 2000 'S' 2001 'T' 2002);
END

-* What is the latest year to be reported?
TABLE FILE GGSALES
SUM MAX.YEAR AS 'MAXYEAR'
ON TABLE SAVE AS SVYEAR
END
-RUN
-READ SVYEAR &MAXYEAR.A4.
-RUN
-TYPE Max Year: &MAXYEAR

-* Create Sales report.
TABLE FILE GGSALES
SUM    DOLLARS
BY     REGION
ACROSS YEAR
ON TABLE SET STYLE *
 TYPE=DATA,        WHEN = YEAR EQ &MAXYEAR, COLOR=RED, $
 TYPE=ACROSSVALUE, WHEN = YEAR EQ &MAXYEAR, COLOR=RED, $
ENDSTYLE
END

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

Thank you, Martin. I had not had the opportunity yet to try David's solution. I thought it was either close or right on target. Thanks for verifying that it should be what I need and adding more detail. Thank you again, David, for your sample. I will not post this as closed until likely next week as I'm in the middle of some unanticipated projects right now and will not be able to try this until sometime then.

I finally returned to this.

However, I ran into a minor challenge.

I know my challenge has to do with the format of my year field, so I'll research converting an alphanumeric field (A4V) to a year. The year is currently in my source table as A4V, and I suspect why the code has not yet worked for me. I've already seen some good posts. I just need to read them. I just didn't want anyone wondering what happened on this.

I appreciate the solutions, and I'm going to leave this open a little while longer until I figure out my date conversion issue. Thanks everyone!

Just in case anyone is wondering how far I got on this, here's my code thus far (minus any conversion code):

DEFINE FILE SALES_TABLE
YEAR/A4=EDIT( SALES_TABLE.SALES_TABLE.SALES_YEAR, '9999');
END

-*What is the latest year to be reported?
TABLE FILE SALES_TABLE
SUM MAX.SALES_TABLE.SALES_TABLE.YEAR AS 'MAXYEAR'
ON TABLE SAVE AS SVYEAR
END
-RUN
-READ SVYEAR &MAXYEAR.A4.
-RUN
-TYPE Max Year: &MAXYEAR

-* Create Sales report.
TABLE FILE SALES_TABLE
SUM HEADCOUNT
BY SECTOR_NAME
ACROSS YEAR
ON TABLE SET STYLE *
TYPE=DATA, WHEN = YEAR EQ &MAXYEAR, COLOR=RGB(0 58 99), $
TYPE=ACROSSVALUE, WHEN = YEAR EQ &MAXYEAR, COLOR=RGB(0 58 99), $
ENDSTYLE
END