I'm developing a report that pulls 8 quarters of data from prior (2006) and current(2007) years. Everything works fine up to the quarters that have not only actuals, but also commit current or forecast current amounts. For instance, Q207 has 2 months of actuals (April & May), and one month of CE which could be either COMMIT_CURRENT or FORECAST_CURRENT. The issue is that my code is adding both CC and FC instead of picking just the first one available (I'm using an IF statement for that). Sorry for the long code below. Appreciate any ideas you guys my have. Thanks
Luiz

ENGINE MSOLAP SET EMPTY ON
*===============================================
-* Budget repor that pulls 8 quarters of data (2 years). Quarters that include CE,
-* should add together actuals + forecast_current (or forecast_future)
-* THIS REPORT IS ONLY PULLING 4 QUARTERS OF DATA TO SIMPLIFY TEST
-*===============================================
-SET &ECHO=ALL;
APP HOLD LUIZ_FINAL

SET EMPTYREPORT=ON
SET HOLDLIST = PRINTONLY
SET PAGE = NOPAGE
SET LINES = 999999
SET SHOWBLANKS = ON
SET CENT-ZERO = ON
-*===============================================
-* DEFINE PRIOR, CURRENT YEAR
-*===============================================
-SET &CURYR =&DATEYY;
-SET &PREVYR =&CURYR - 1;
-SET &FUTYR =&CURYR + 1;
-*===============================================
-* GATHER DATA FROM 2006 through 2007 FOR ACTUALS, COMMIT_CURRENT & FORECAST_CURRENT
-* quarters in 2006 will have actuals only
-*===============================================
FILEDEF BUDGET1 DISK BUDGET1.DAT
DEFINE FILE NEW_STD_COSTING_STG
MON_M/I6=EDIT(MONTH);
VAR1/A2=EDIT(MON_M,'$$$$99');
QVAR/A2=EDIT(QUARTER,'$$$$99');
END

TABLE FILE NEW_STD_COSTING_STG
PRINT
COMPUTE NEW_AMT/D12 = AMOUNT / 1000;
YEAR
QVAR
VAR1
DATATYPE_PARENT
COST_POOL_FAMILY
WHERE YEAR GE '&PREVYR';
WHERE YEAR LE '&CURYR';
WHERE TOTAL_COMPANY NE 'AFFILIATE_CUSSEG';
WHERE ( DATATYPE_PARENT EQ 'ACTUALS' OR 'COMMIT_CURRENT' OR 'FORECAST_CURRENT');
ON TABLE HOLD AS BUDGET1 FORMAT ALPHA
ON TABLE SET HOLDLIST ALL
END

-*===============================================================================================
-* AMOUNTS: Q1 & Q2 OF PREVYR (2006) AND Q1 & Q2 (APRIL & MAY ACTUALS + JUNE COMMIT_CURRENT OR FORECAST_CURRENT)
-*===============================================================================================

TABLE FILE BUDGET1
PRINT
COMPUTE JAN_PRI/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE JAN_ACT/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE JAN_CE/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN NEW_AMT ELSE 0;
COMPUTE JAN_BUD/D12.1 =IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN NEW_AMT ELSE 0;

COMPUTE FEB_PRI/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE FEB_ACT/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE FEB_CE/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN NEW_AMT ELSE 0;
COMPUTE FEB_BUD/D12.1 =IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN NEW_AMT ELSE 0;

COMPUTE MAR_PRI/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE MAR_ACT/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE MAR_CE/D12.1= IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN NEW_AMT ELSE 0;
COMPUTE MAR_BUD/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN NEW_AMT ELSE 0;

COMPUTE APR_PRI/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE APR_ACT/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE APR_CE/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN NEW_AMT ELSE 0;
COMPUTE APR_BUD/D12.1 =IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN NEW_AMT ELSE 0;

COMPUTE MAY_PRI/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE MAY_ACT/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE MAY_CE/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN NEW_AMT ELSE 0;
COMPUTE MAY_BUD/D12.1 =IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN NEW_AMT ELSE 0;

COMPUTE JUN_PRI/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE JUN_ACT/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN NEW_AMT ELSE 0;
COMPUTE JUN_CE/D12.1= IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN NEW_AMT ELSE 0;
COMPUTE JUN_BUD/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN NEW_AMT ELSE 0;
BY YEAR
BY QVAR
BY VAR1
BY DATATYPE_PARENT
ON TABLE HOLD AS BUDGET2 FORMAT ALPHA
END

-*===============================================================================================
-* QUARTERS
-*=======================================================================================================
DEFINE FILE BUDGET2

QTR/A2 = IF (YEAR EQ '&PREVYR' OR YEAR EQ '&CURYR')AND VAR1 EQ '01' OR '02' OR '03' THEN 'Q1' ELSE
IF (YEAR EQ '&PREVYR' OR YEAR EQ '&CURYR') AND VAR1 EQ '04' OR '05' OR '06' THEN 'Q2' ELSE
IF (YEAR EQ '&PREVYR' OR YEAR EQ '&CURYR') AND VAR1 EQ '07' OR '08' OR '09' THEN 'Q3' ELSE 'Q4';
END

TABLE FILE BUDGET2
SUM
-* Q1 prior year:
JAN_PRI
FEB_PRI
MAR_PRI
COMPUTE Q1PREVYR/D12.1 = JAN_PRI + FEB_PRI + MAR_PRI; AS 'Q1&PREVYR'
-* Q2 prior year:
APR_PRI
MAY_PRI
JUN_PRI
COMPUTE Q2PREVYR/D12.1 = JAN_PRI + FEB_PRI + MAR_PRI; AS 'Q2&PREVYR'

-* Q1 CURRENT YEAR (ALL THREE MONTHS ARE ACTUALS):
COMPUTE CUM1/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'ACTUALS' THEN JAN_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN JAN_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN JAN_BUD ELSE 0;

COMPUTE CUM2/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'ACTUALS' THEN FEB_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN FEB_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN FEB_BUD ELSE 0;

COMPUTE CUM3/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'ACTUALS' THEN MAR_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN MAR_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN MAR_BUD ELSE 0;
COMPUTE Q1CURYR/D12.1 = CUM1 + CUM2 + CUM3; AS 'Q1&CURYR'

-* Q2 CURRENT YEAR (APRIL & MAY ARE ACTUALS, JUNE IS EITHER COMMIT_CURRENT OR FORECAST_CURRENT):
COMPUTE CUM4/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'ACTUALS' THEN JAN_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN JAN_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN JAN_BUD ELSE 0;

COMPUTE CUM5/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'ACTUALS' THEN FEB_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN FEB_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN FEB_BUD ELSE 0;

COMPUTE CUM6/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'ACTUALS' THEN MAR_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN MAR_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN MAR_BUD ELSE 0;
COMPUTE Q2CURYR/D12.1 = CUM4 + CUM5 + CUM6; AS 'Q2&CURYR'

-*BY YEAR
-*BY QVAR
BY QTR
BY DATATYPE_PARENT
ON TABLE PCHOLD FORMAT EXL2K
END

quote:

PRINT
COMPUTE NEW_AMT/D12 = AMOUNT / 1000;

I wonder if this is your problem, regardless of the type you always make NEW_AMT equal to AMOUNT / 1000. I'm assuming you are trying to display only thousands of dollars.

Leah,
When I display the amounts by datatype (ACTUALS, FORECAST_CURRENT & COMMIT_CURRENT), they display correctly. Are you saying that I should try to make the "amounts" distinct for each datatype?

quote:

Leah,
When I display the amounts by datatype (ACTUALS, FORECAST_CURRENT & COMMIT_CURRENT), they display correctly. Are you saying that I should try to make the "amounts" distinct for each datatype?

Yes.

Leah,
I have changed the compute(s) to reflect the different datatype_parent(s) being used. However, I'm still not able to get the quarters to work. I'm including the changed code below. Thanks

TABLE FILE NEW_STD_COSTING_STG
SUM
COMPUTE AMT_ACT/D12 = IF DATATYPE_PARENT EQ 'ACTUALS' THEN AMOUNT / 1000 ELSE 0;
COMPUTE AMT_CC/D12 = IF DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMOUNT / 1000 ELSE 0;
COMPUTE AMT_FC/D12 = IF DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMOUNT / 1000 ELSE 0;
BY YEAR
BY QVAR
BY VAR1
BY DATATYPE_PARENT
BY COST_POOL_FAMILY
WHERE YEAR GE '&PREVYR';
WHERE YEAR LE '&CURYR';
WHERE TOTAL_COMPANY NE 'AFFILIATE_CUSSEG';
WHERE ( DATATYPE_PARENT EQ 'ACTUALS' OR 'COMMIT_CURRENT' OR 'FORECAST_CURRENT');
ON TABLE HOLD AS BUDGET1 FORMAT ALPHA
ON TABLE SET HOLDLIST ALL
END


TABLE FILE BUDGET1
PRINT
COMPUTE JAN_PRI/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE JAN_ACT/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE JAN_CE/D12.1= IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMT_CC ELSE 0;
COMPUTE JAN_BUD/D12.1 =IF VAR1 EQ '01' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMT_FC ELSE 0;

COMPUTE FEB_PRI/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE FEB_ACT/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE FEB_CE/D12.1= IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMT_CC ELSE 0;
COMPUTE FEB_BUD/D12.1 =IF VAR1 EQ '02' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMT_FC ELSE 0;

COMPUTE MAR_PRI/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE MAR_ACT/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE MAR_CE/D12.1= IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMT_CC ELSE 0;
COMPUTE MAR_BUD/D12.1=IF VAR1 EQ '03' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMT_FC ELSE 0;

COMPUTE APR_PRI/D12.1= IF VAR1 EQ '04' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE APR_ACT/D12.1= IF VAR1 EQ '04' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE APR_CE/D12.1= IF VAR1 EQ '04' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMT_CC ELSE 0;
COMPUTE APR_BUD/D12.1 =IF VAR1 EQ '04' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMT_FC ELSE 0;

COMPUTE MAY_PRI/D12.1= IF VAR1 EQ '05' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE MAY_ACT/D12.1= IF VAR1 EQ '05' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE MAY_CE/D12.1= IF VAR1 EQ '05' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMT_ACT ELSE 0;
COMPUTE MAY_BUD/D12.1 =IF VAR1 EQ '05' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMT_FC ELSE 0;

COMPUTE JUN_PRI/D12.1=IF VAR1 EQ '06' AND YEAR EQ '&PREVYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE JUN_ACT/D12.1=IF VAR1 EQ '06' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'ACTUALS' THEN AMT_ACT ELSE 0;
COMPUTE JUN_CE/D12.1= IF VAR1 EQ '06' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN AMT_CC ELSE 0;
COMPUTE JUN_BUD/D12.1=IF VAR1 EQ '06' AND YEAR EQ '&CURYR' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN AMT_FC ELSE 0;
BY YEAR
BY QVAR
BY VAR1
BY DATATYPE_PARENT
ON TABLE HOLD AS BUDGET2 FORMAT ALPHA
END

-* QUARTERS

DEFINE FILE BUDGET2

QTR/A2 = IF (YEAR EQ '&PREVYR' OR YEAR EQ '&CURYR') AND VAR1 EQ '01' OR '02' OR '03' THEN 'Q1' ELSE
IF (YEAR EQ '&PREVYR' OR YEAR EQ '&CURYR') AND VAR1 EQ '04' OR '05' OR '06' THEN 'Q2' ELSE
IF (YEAR EQ '&PREVYR' OR YEAR EQ '&CURYR') AND VAR1 EQ '07' OR '08' OR '09' THEN 'Q3' ELSE 'Q4';
END

TABLE FILE BUDGET2
SUM
-* Q1 prior year:
JAN_PRI
FEB_PRI
MAR_PRI
COMPUTE Q1PREVYR/D12.1 = JAN_PRI + FEB_PRI + MAR_PRI; AS 'Q1&PREVYR'
-* Q2 prior year:
APR_PRI
MAY_PRI
JUN_PRI
COMPUTE Q2PREVYR/D12.1 = JAN_PRI + FEB_PRI + MAR_PRI; AS 'Q2&PREVYR'

-* Q1 CURRENT YEAR (ALL THREE MONTHS ARE ACTUALS):
COMPUTE CUM1/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'ACTUALS' THEN JAN_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN JAN_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN JAN_BUD ELSE 0;

COMPUTE CUM2/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'ACTUALS' THEN FEB_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN FEB_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN FEB_BUD ELSE 0;

COMPUTE CUM3/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'ACTUALS' THEN MAR_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN MAR_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN MAR_BUD ELSE 0;
COMPUTE Q1CURYR/D12.1 = CUM1 + CUM2 + CUM3; AS 'Q1&CURYR'

-* Q2 CURRENT YEAR (APRIL & MAY ARE ACTUALS, JUNE IS EITHER COMMIT_CURRENT OR FORECAST_CURRENT):
COMPUTE CUM4/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'ACTUALS' THEN JAN_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN JAN_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN JAN_BUD ELSE 0;

COMPUTE CUM5/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'ACTUALS' THEN FEB_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN FEB_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN FEB_BUD ELSE 0;

COMPUTE CUM6/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'ACTUALS' THEN MAR_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN MAR_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN MAR_BUD ELSE 0;
COMPUTE Q2CURYR/D12.1 = CUM4 + CUM5 + CUM6; AS 'Q2&CURYR'

-*BY YEAR
-*BY QVAR
-*BY QTR
-*BY DATATYPE_PARENT
ON TABLE PCHOLD FORMAT EXL2K
END

When you say quarters don't work? What exactly do you mean. If you just exit the fex at the point you have created the first file and temporarily make it an output to the screen, does the data look okay at that point?

Leah,
The two hold files (budget1 & budget2) work fine; however, when I bring the months together to create the quarters (last part of the fex), it doesn't pull anything for Q2 of 2007 (which has 2 months of actuals, and one month of CE). Thanks

I have a question on this code from the last step. Since you already have identified what is current, actuals and so on for each month in the budget2 why are you doing all the extra checking here again. Are you trying to get a total of the items which the sum is already doing for you, but with a different name, as since you are using a compute, I believe you have to have all the by fields to do that checking properly?

What do you get if you just sum the fields from the budget2 file by the QTR field in the define without all the computes. At this point I am a little puzzled why you wouldn't see Q2.

quote:

-* Q1 CURRENT YEAR (ALL THREE MONTHS ARE ACTUALS):
COMPUTE CUM1/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'ACTUALS' THEN JAN_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN JAN_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '01' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN JAN_BUD ELSE 0;

COMPUTE CUM2/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'ACTUALS' THEN FEB_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN FEB_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '02' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN FEB_BUD ELSE 0;

COMPUTE CUM3/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'ACTUALS' THEN MAR_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN MAR_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '03' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN MAR_BUD ELSE 0;
COMPUTE Q1CURYR/D12.1 = CUM1 + CUM2 + CUM3; AS 'Q1&CURYR'

-* Q2 CURRENT YEAR (APRIL & MAY ARE ACTUALS, JUNE IS EITHER COMMIT_CURRENT OR FORECAST_CURRENT):
COMPUTE CUM4/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'ACTUALS' THEN JAN_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN JAN_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '04' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN JAN_BUD ELSE 0;

COMPUTE CUM5/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'ACTUALS' THEN FEB_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN FEB_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '05' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN FEB_BUD ELSE 0;

COMPUTE CUM6/D12.1 = IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'ACTUALS' THEN MAR_ACT ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'COMMIT_CURRENT' THEN MAR_CE ELSE
IF YEAR EQ '&CURYR' AND VAR1 EQ '06' AND DATATYPE_PARENT EQ 'FORECAST_CURRENT' THEN MAR_BUD ELSE 0;
COMPUTE Q2CURYR/D12.1 = CUM4 + CUM5 + CUM6; AS 'Q2&CURYR'

-*BY YEAR

Agreed. I was able to get Q207, by changing my code. Thanks for all your help.
Luiz

-SET &CUR_MO=&DATEM;
-* Q1 CURRENT YEAR (ALL THREE MONTHS ARE ACTUALS):
COMPUTE CUM1/D12.1 = IF VAR1 GE '&CUR_MO' AND 'AMT_ACT' NE '0' THEN JAN_ACT ELSE
IF VAR1 GE '&CUR_MO' AND 'AMT_FC' NE '0' THEN JAN_CE ELSE
IF 'AMT_CC' NE '0' THEN JAN_BUD ELSE 0;

COMPUTE CUM2/D12.1 = IF VAR1 GE '&CUR_MO' AND 'AMT_ACT' NE '0' THEN FEB_ACT ELSE
IF VAR1 GE '&CUR_MO' AND 'AMT_FC' NE '0' THEN FEB_CE ELSE
IF 'AMT_CC' NE '0' THEN FEB_BUD ELSE 0;

COMPUTE CUM3/D12.1 = IF VAR1 GE '&CUR_MO' AND 'AMT_ACT' NE '0' THEN MAR_ACT ELSE
IF VAR1 GE '&CUR_MO' AND 'AMT_FC' NE '0' THEN MAR_CE ELSE
IF 'AMT_CC' NE '0' THEN MAR_BUD ELSE 0;
COMPUTE Q1CURYR/D12.1 = CUM1 + CUM2 + CUM3; AS 'Q1&CURYR'

-* Q2 CURRENT YEAR (APRIL & MAY ARE ACTUALS, JUNE IS EITHER COMMIT_CURRENT OR FORECAST_CURRENT):
COMPUTE CUM4/D12.1 = IF VAR1 LT '&CUR_MO' AND 'AMT_ACT' NE '0' THEN APR_ACT ELSE
IF VAR1 GE '&CUR_MO' AND 'AMT_FC' NE '0' THEN APR_CE ELSE
IF 'AMT_CC' NE '0' THEN APR_BUD ELSE 0;

COMPUTE CUM5/D12.1 = IF VAR1 LT '&CUR_MO' AND 'AMT_ACT' NE '0' THEN MAY_ACT ELSE
IF VAR1 GE '&CUR_MO' AND 'AMT_FC' NE '0' THEN MAY_CE ELSE
IF 'AMT_CC' NE '0' THEN MAY_BUD ELSE 0;

COMPUTE CUM6/D12.1 = IF VAR1 LT '&CUR_MO' AND 'AMT_ACT' NE '0' THEN JUN_ACT ELSE
IF VAR1 GE '&CUR_MO' AND 'AMT_FC' NE '0' THEN JUN_CE ELSE
IF 'AMT_CC' NE '0' THEN JUN_BUD ELSE 0;
COMPUTE Q2CURYR/D12.1 = CUM4 + CUM5 + CUM6; AS 'Q2&CURYR'

Glad to hear and you are welcome.