In the join below, we are joining a GLACTUAL file with a GLBUDGET file. We have created a define for a heading based on a period amper variable that is also used for the time periods in the Month to Date, QTD and YTD amounts.

I some cases we have actual amounts and no budget. When this happens, the amounts are correct, but the MPERIOD, QPERIOD and YPERIOD return a value of zero. If there are amounts in both files, the heading works.

I would think that the MPERIOD is an independant define that has nothing to do with the joins.

We work in the Finance department so we are not that technical. Here's the code for those who want to play along at home:

-* File test_actual_vs_budget.fex
-INCLUDE SET_APP_PATH
SET PRINTPLUS = OFF
SET CNOTATION = PRINTONLY
SET NODATA = 0

JOIN
 LEFT_OUTER GLACTUAL.GLACTUAL.FISCAL_YEAR AND GLACTUAL.GLACTUAL.AU_MATRIX_CAT
 AND GLACTUAL.GLACTUAL.AU_MX_VALUE AND GLACTUAL.GLACTUAL.GLACC_MATRIX_CAT
 AND GLACTUAL.GLACTUAL.GLACC_MX_VALUE IN GLACTUAL TO MULTIPLE
 GLBUDGET.GLBUDGET.FISCAL_YEAR AND GLBUDGET.GLBUDGET.AU_MATRIX_CAT
 AND GLBUDGET.GLBUDGET.AU_MX_VALUE AND GLBUDGET.GLBUDGET.GLACC_MATRIX_CAT
 AND GLBUDGET.GLBUDGET.GLACC_MX_VALUE IN GLBUDGET TAG J0 AS J0
 END
DEFINE FILE GLACTUAL
PERIOD/I4=.
FY/YY=&FY;
CFY/YY=FY;
LFY/YY=FY-1;
MTDACT/D15CSB=IF PERIOD EQ 1 THEN NET01 ELSE IF PERIOD EQ 2 THEN NET02 ELSE IF PERIOD EQ 3 THEN NET03 ELSE IF PERIOD EQ 4 THEN NET04 ELSE IF PERIOD EQ 5 THEN NET05 ELSE IF PERIOD EQ 6 THEN NET06 ELSE IF PERIOD EQ 7 THEN NET07 ELSE IF PERIOD EQ 8 THEN NET08 ELSE IF PERIOD EQ 9 THEN NET09 ELSE IF PERIOD EQ 10 THEN NET10 ELSE IF PERIOD EQ 11 THEN NET11 ELSE IF PERIOD EQ 12 THEN NET12 ELSE 0;
QTDACT/D15CSB=IF PERIOD EQ 1 THEN NET01 ELSE IF PERIOD EQ 2 THEN NET01 + NET02 ELSE IF PERIOD EQ 3 THEN NET01 + NET02 + NET03 ELSE IF PERIOD EQ 4 THEN NET04 ELSE IF PERIOD EQ 5 THEN NET04 + NET05 ELSE IF PERIOD EQ 6 THEN NET04 + NET05 + NET06 ELSE IF PERIOD EQ 7 THEN NET07 ELSE IF PERIOD EQ 8 THEN NET07 + NET08 ELSE IF PERIOD EQ 9 THEN NET07 + NET08 + NET09 ELSE IF PERIOD EQ 10 THEN NET10 ELSE IF PERIOD EQ 11 THEN NET10 + NET11 ELSE IF PERIOD EQ 12 THEN NET10 + NET11 + NET12 ELSE 0;
YTDACT/D15CSB=IF PERIOD EQ 1 THEN NET01 ELSE IF PERIOD EQ 2 THEN NET01 + NET02 ELSE IF PERIOD EQ 3 THEN NET01 + NET02 + NET03 ELSE IF PERIOD EQ 4 THEN NET01 + NET02 + NET03 + NET04 ELSE IF PERIOD EQ 5 THEN NET01 + NET02 + NET03 + NET04 + NET05 ELSE IF PERIOD EQ 6 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 ELSE IF PERIOD EQ 7 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 + NET07 ELSE IF PERIOD EQ 8 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 + NET07 + NET08 ELSE IF PERIOD EQ 9 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 + NET07 + NET08 + NET09 ELSE IF PERIOD EQ 10 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 + NET07 + NET08 + NET09 + NET10 ELSE IF PERIOD EQ 11 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 + NET07 + NET08 + NET09 + NET10 + NET11 ELSE IF PERIOD EQ 12 THEN NET01 + NET02 + NET03 + NET04 + NET05 + NET06 + NET07 + NET08 + NET09 + NET10 + NET11 + NET12 ELSE 0;
MTDBUD/D15CSB=IF PERIOD EQ 1 THEN BUD01 ELSE IF PERIOD EQ 2 THEN BUD02 ELSE IF PERIOD EQ 3 THEN BUD03 ELSE IF PERIOD EQ 4 THEN BUD04 ELSE IF PERIOD EQ 5 THEN BUD05 ELSE IF PERIOD EQ 6 THEN BUD06 ELSE IF PERIOD EQ 7 THEN BUD07 ELSE IF PERIOD EQ 8 THEN BUD08 ELSE IF PERIOD EQ 9 THEN BUD09 ELSE IF PERIOD EQ 10 THEN BUD10 ELSE IF PERIOD EQ 11 THEN BUD11 ELSE IF PERIOD EQ 12 THEN BUD12 ELSE 0;
QTDBUD/D15CSB=IF PERIOD EQ 1 THEN BUD01 ELSE IF PERIOD EQ 2 THEN BUD01 + BUD02 ELSE IF PERIOD EQ 3 THEN BUD01 + BUD02 + BUD03 ELSE IF PERIOD EQ 4 THEN BUD04 ELSE IF PERIOD EQ 5 THEN BUD04 + BUD05 ELSE IF PERIOD EQ 6 THEN BUD04 + BUD05 + BUD06 ELSE IF PERIOD EQ 7 THEN BUD07 ELSE IF PERIOD EQ 8 THEN BUD07 + BUD08 ELSE IF PERIOD EQ 9 THEN BUD07 + BUD08 + BUD09 ELSE IF PERIOD EQ 10 THEN BUD10 ELSE IF PERIOD EQ 11 THEN BUD10 + BUD11 ELSE IF PERIOD EQ 12 THEN BUD10 + BUD11 + BUD12 ELSE 0;
YTDBUD/D15CSB=IF PERIOD EQ 1 THEN BUD01 ELSE IF PERIOD EQ 2 THEN BUD01 + BUD02 ELSE IF PERIOD EQ 3 THEN BUD01 + BUD02 + BUD03 ELSE IF PERIOD EQ 4 THEN BUD01 + BUD02 + BUD03 + BUD04 ELSE IF PERIOD EQ 5 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 ELSE IF PERIOD EQ 6 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 ELSE IF PERIOD EQ 7 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 + BUD07 ELSE IF PERIOD EQ 8 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 + BUD07 + BUD08 ELSE IF PERIOD EQ 9 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 + BUD07 + BUD08 + BUD09 ELSE IF PERIOD EQ 10 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 + BUD07 + BUD08 + BUD09 + BUD10 ELSE IF PERIOD EQ 11 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 + BUD07 + BUD08 + BUD09 + BUD10 + BUD11 ELSE IF PERIOD EQ 12 THEN BUD01 + BUD02 + BUD03 + BUD04 + BUD05 + BUD06 + BUD07 + BUD08 + BUD09 + BUD10 + BUD11 + BUD12 ELSE 0;
MPERIOD/A20=DECODE PERIOD( 1 'MONTH ENDED 10/31' 2 'MONTH ENDED 11/30' 3 'MONTH ENDED 12/31' 4 'MONTH ENDED 1/31' 5 'MONTH ENDED 2/28' 6 'MONTH ENDED 3/31' 7 'MONTH ENDED 4/30' 8 'MONTH ENDED 5/31' 9 'MONTH ENDED 6/30' 10 'MONTH ENDED 7/31' 11 'MONTH ENDED 8/31' 12 'MONTH ENDED 9/30' );
QPERIOD/A20=DECODE PERIOD( 1 '1 MONTH ENDED 10/31' 2 '2 MONTHS ENDED 11/30' 3 '3 MONTHS ENDED 12/31' 4 '1 MONTH ENDED 1/31' 5 '2 MONTHS ENDED 2/28' 6 '3 MONTHS ENDED 3/31' 7 '1 MONTH ENDED 4/30' 8 '2 MONTHS ENDED 5/31' 9 '3 MONTHS ENDED 6/30' 10 '1 MONTH ENDED 7/31' 11 '2 MONTHS ENDED 8/31' 12 '3 MONTHS ENDED 9/30' );
YPERIOD/A20=DECODE PERIOD( 1 '1 MONTH ENDED 10/31' 2 '2 MONTHS ENDED 11/30' 3 '3 MONTHS ENDED 12/31' 4 '4 MONTHS ENDED 1/31' 5 '5 MONTHS ENDED 2/28' 6 '6 MONTHS ENDED 3/31' 7 '7 MONTHS ENDED 4/30' 8 '8 MONTHS ENDED 5/31' 9 '9 MONTHS ENDED 6/30' 10 '10 MONTHS ENDED 7/31' 11 '11 MONTHS ENDED 8/31' 12 '12 MONTHS ENDED 9/30' );
BUDDESC/A35=DECODE J0.GLBUDGET.BUDGET_NBR( 1 'Budget Version: Original' 2 'Budget Version: Revised MMM YY' 3 'Budget Version: Revised MMM YY' 4 'Budget Version: Revised MMM YY' );
END
TABLE FILE GLACTUAL
SUM
-INCLUDE  include_col_glactual.fex
BY 'GLACTUAL.GLACTUAL.AU_MX_VALUE' NOPRINT
-INCLUDE include_row_glactual_opinc.fex

ON GLACTUAL.GLACTUAL.AU_MX_VALUE PAGE-BREAK
HEADING
"Date : &DATEtrMDYY  Time: &TOD<+0> <+0>NBTY, INC. AND SUBSIDIARIES<+0> "
"Report: &FOCFEXNAME<+0> <+0>STATEMENT OF PROFIT (LOSS) VS. PLAN<+0> "
"<GLACTUAL.GLACTUAL.AU_MX_VALUE<J0.GLBUDGET.BUDDESC GAAP BASIS<+0> "
"Fiscal Year : &FY<MPERIOD<QPERIOD<YPERIOD"
WHERE ( GLACTUAL.GLACTUAL.FISCAL_YEAR EQ FY ) AND ( GLACTUAL.GLACTUAL.AU_MATRIX_CAT EQ 'DIVISIONNBTY' ) AND ( GLACTUAL.GLACTUAL.GLACC_MATRIX_CAT EQ 'SECSUM' OR 'EBITDA' ) AND ( J0.GLBUDGET.BUDGET_NBR EQ &BUDGET_NBR.(<Orginal,1>,<Revision 1 do not use,2>,<Revision 2 do not use,3>,<Revision 3 do not use,4>).BUDGET_NBR. );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report for Adobe Flash Player,FLEX>,<Active Report for PDF,APDF>,<PowerPoint,PPT>).Select type of display output.
ON TABLE SET STYLE * 
 

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

Since you haven't explicitly placed your DEFINEs in the first table, they are likely defaulting to the second table, where they become dependent on the existence of data in that table. Try placing the DEFINEs explicitly in the first table:

 PERIOD/I4 WITH GLACTUAL.GLACTUAL.FISCAL_YEAR = &PERIOD ;
 FY/YY WITH GLACTUAL.GLACTUAL.FISCAL_YEAR = &FY ;
 CFY/YY      = FY ;
 LFY/YY      = FY - 1 ;

quote:

PERIOD/I4 WITH GLACTUAL.GLACTUAL.FISCAL_YEAR = &PERIOD ;
FY/YY WITH GLACTUAL.GLACTUAL.FISCAL_YEAR = &FY ;

It works !!! Thanks for the help.