I'm calculating a difference between two fields under an across as such:

TABLE FILE MAZCV
SUM FIELD1
FIELD2
BY FIELD3
ACROSS FIELD4
COMPUTE TESTDIFF/I9 = C2 - C1;
END

This works fine. But I would like to filter on the computed field. For example, WHERE TESTDIFF LE 10.

TABLE FILE MAZCV
SUM FIELD1
FIELD2
BY FIELD3
ACROSS FIELD4
COMPUTE TESTDIFF/I9 = C2 - C1;
WHERE TOTAL TESTDIFF LE 10
END

It gives me a weird error message. Is there anyway to put a WHERE clause on a computed field after an across ?

This is the error message I get:

(FOC14069) SYNTAX ERROR ON LINE 1 AT '=' -- Literal expected

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

Try this:

 

TABLE FILE EMPDATA
SUM
     SALARY
BY DEPT
ACROSS DIV
COMPUTE TESTDIFF/D12.2 = C2 - C1;
ON TABLE HOLD
END		
	 
TABLE FILE HOLD
PRINT
     TESTDIFF
BY DEPT
WHERE TESTDIFF GT 0
END
 

Try this with and without the WHERE.

Regards,

John

Thanks John.

The problem with your example is that you're losing the across field. No where in the output do you see the DIV.

Basically I want the exact same output of your first query but only show the results where TESTDIFF > 0

Cribbing from John's code, try this:

DEFINE FILE EMPDATA
SALINCR/D12.2=SALARY * 1.05;
END
TABLE FILE EMPDATA
SUM
     SALINCR SALARY
COMPUTE TESTDIFF/D12.2 = C1 - C2;
BY DEPT
BY DIV
WHERE TOTAL TESTDIFF GE 5000
ON TABLE HOLD
END			 
TABLE FILE HOLD
SUM TESTDIFF
BY DEPT
ACROSS DIV
END

jif,

As you can see, the DIF field is not accessible in your TABLE request.

So, you have to create a HOLD file to be able to test the DIF field. Here is a possibility, using the CAR file.

  
SET ASNAMES=ON
TABLE FILE CAR
IF SEATS NE 5
SUM SALES AS 'SEATS='
BY COUNTRY
ACROSS SEATS COMPUTE DIF/I6=C1 - C2;
ON TABLE HOLD
END
JOIN COUNTRY IN HOLD TO COUNTRY IN CAR AS A_
TABLE FILE HOLD
IF DIF NE 0
IF SEATS NE 5
SUM SALES
BY COUNTRY
ACROSS SEATS COMPUTE DIF/I6=C1 - C2;
END

If you do not need to show the ACROSS field then this is also possible:

  
SET ASNAMES=ON
TABLE FILE CAR
IF SEATS NE 5
SUM SALES AS 'SEATS='
BY COUNTRY
ACROSS SEATS COMPUTE DIF/I6=C1 - C2;
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
IF DIF NE 0
END

Good luck....

quote:

Originally posted by GinnyJakes:
Cribbing from John's code, try this:

DEFINE FILE EMPDATA
SALINCR/D12.2=SALARY * 1.05;
END
TABLE FILE EMPDATA
SUM
     SALINCR SALARY
COMPUTE TESTDIFF/D12.2 = C1 - C2;
BY DEPT
BY DIV
WHERE TOTAL TESTDIFF GE 5000
ON TABLE HOLD
END			 
TABLE FILE HOLD
SUM TESTDIFF
BY DEPT
ACROSS DIV
END

This seems to work fine. Thanks.

I'll also look at your suggestion Danny. Thanks as well!

OK. After making something work, I have this issue using the technique above, there's one big show stopper.

Basically my master file has a key with a year and 12 months of data. The user selects a month on the screen and the report will show him the 12 months along with the variance between the month selected and the previous one.

Using the technique above, this works fine. This is the way I coded it to make the filter of the variance work (user selected March):

TABLE FILE MYTABLE
SUM
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC

COMPUTE VARIANCE/I9 = C3 - C2;

BY FIELD1
BY YEAR

WHERE YEAR EQ '2008' OR '2009'
WHERE TOTAL VARIANCE GE 100

ON TABLE HOLD
END

TABLE FILE HOLD
SUM
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC

BY FIELD1
ACROSS YEAR
COMPUTE VARIANCE/I9 = C3 - C2;

END

Works great. Except for one thing. If the user selects January, I'm screwed.

Do you need to calculate against December? You might be able to do something with the LAST function.

Or do you need to do a rolling 12 months? That makes the logic a bit more complex but doable with some Dialogue Manager.

Or you can test for JAN and make the variance zero.

That's right, I would need to calculate against December (of the previous year of course).

On the code above, I calculate my columns using dialog manager.

Ok, here's how I did it.

-* (WHERE &MONTH = 1 for Jan, 2 for Feb, ...)
-SET &STARTCOL = &MONTH;
-SET &PREVCOL = &STARTCOL - 1;

-SET &COL1 = 'C' | &STARTCOL;
-SET &COL2 = 'C' | &PREVCOL;

TABLE FILE MAZCV
SUM
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
COMPUTE Variance/I9 = IF &MONTH EQ 1 THEN C1 - LAST C12 ELSE &COL1 - &COL2;

BY FIELD1
BY YEAR
WHERE YEAR EQ '2007' OR '2008'
WHERE TOTAL Variance GE 100;
ON TABLE HOLD AS TEMP1
END

-SET &NBRYEARS = &LINES;

-SET &STARTCOL = &MONTH + ( (&NBRYEARS - 1) * 12 );
-SET &PREVCOL = &STARTCOL - 1;
-SET &PREVYEAR = &STARTCOL - 12;

-SET &COL1 = 'C' | &STARTCOL;
-SET &COL2 = 'C' | &PREVCOL;

SET BYDISPLAY = ON
SET NODATA = 0

TABLE FILE TEMP1
SUM
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
BY FIELD1
ACROSS YEAR
COMPUTE Variance/I9 = IF &NBRYEARS LE 1 THEN &COL1 ELSE &COL1 - &COL2;
END