Hi all,

I am trying to define a new field and use it as ACROSS. What I am looking for is; first ACROSS section is the sales of model '2002 2 DOOR AUTO', second ACROSS section is the sales of all 'BMW' brand cars, which should include the sales of '2002 2 DOOR AUTO', and the last ACROSS column would be the sales of all CAR, which should include sales of 'BMW' and '2002 2 DOOR AUTO'.

I started to define them using the standard IF, THEN, ELSE statement as below:

DEFINE FILE IBISAMP/CAR
Model_Brand_AllCar/A30=IF MODEL EQ '2002 2 DOOR AUTO' 
THEN '2002 2 DOOR AUTO' 
ELSE IF CAR EQ 'BMW' 
THEN 'BMW' ELSE 'xALL CAR';
END
TABLE FILE IBISAMP/CAR
SUM SALES
BY COUNTRY
ACROSS Model_Brand_AllCar AS 'SALES'
ON TABLE SUMMARIZE
END 

But using this code the sales of 'xALL CAR' will exclude sales of 'BMW' and sales of 'BMW' will exclude sales of '2002 2 DOOR AUTO'

Then I tried to define 3 different new fields and have 3 ACROSS as below, but it still doesnt seem to work.

DEFINE FILE IBISAMP/CAR
 v_MODEL/A30=IF MODEL EQ '2002 2 DOOR AUTO' THEN '2002 2 DOOR AUTO' ELSE IF MODEL EQ 'XYZ' THEN 'XYZ';
 v_BRAND/A30=IF CAR EQ 'BMW' THEN 'BMW' ELSE IF MODEL EQ 'XYZ' THEN 'XYZ';
 v_AllCar/A30= IF SALES NE 0 THEN 'ALL CAR' ELSE 'XYZ'
 END
TABLE FILE IBISAMP/CAR
SUM SALES
BY COUNTRY
ACROSS v_AllCar AS ''
ACROSS v_BRAND AS ''
ACROSS v_MODEL AS ''
ON TABLE SUMMARIZE
END 

Does anyone have a solution to what I am looking for?

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

Problem is that ACROSS divides a population, so any data instance included in the report will contribute to only one column. Nothing gets double-counted. What you want requires that one model be triple-counted, and all other BMW models double-counted.

So, if you want to use ACROSS syntax, you have to pre-compute the sum for each bin.

Here's one way...

SET ASNAMES = ON
DEFINE FILE CAR
BIN1/A1='1';
BIN2/A1='2';
BIN3/A1='3';
END

TABLE FILE CAR
SUM SALES BY COUNTRY BY BIN1 AS BIN
WHERE MODEL EQ '2002 2 DOOR AUTO' ;
ON TABLE HOLD AS HOLD1
RUN
SUM SALES BY COUNTRY BY BIN2 AS BIN
WHERE CAR EQ 'BMW';
ON TABLE HOLD AS HOLD2
RUN
SUM SALES BY COUNTRY BY BIN3 AS BIN
ON TABLE HOLD AS HOLD3
END

TABLE FILE HOLD1
SUM SALES BY COUNTRY BY BIN
ON TABLE HOLD AS HOLD4
MORE
FILE HOLD2
MORE
FILE HOLD3
END

TABLE FILE HOLD4
SUM SALES 
BY COUNTRY
ACROSS BIN
ON TABLE SUMMARIZE
END

The same can be accomplished in fewer steps using "McGuyver".

Using McGuyver technique:

EX MAKESEQ FIRST=1,LAST=3,COUNTER=BIN
JOIN CLEAR *
JOIN BLANK WITH SALES IN CAR TO BLANK IN FSEQ

DEFINE FILE CAR
BLANK/A1 WITH SALES =;
XSALES/D12.2 =
IF (BIN EQ 1 AND MODEL EQ '2002 2 DOOR AUTO')
OR (BIN EQ 2 AND CAR EQ 'BMW')
OR (BIN EQ 3)
 THEN SALES
 ELSE 0;
END

TABLE FILE CAR
SUM XSALES AS SALES
BY COUNTRY ACROSS BIN
WHERE BIN FROM 1 TO 3 ;
ON TABLE SUMMARIZE
END

===> (See this post for makeseq.fex) <===

This message has been edited. Last edited by: j.gross,

Thank you j.gross! That is exactly what I was looking for.

I tried to use the McGyver technique but I got the following error message,

ibi.webfoc.wfmre.mrutil.WFMRError: E:\ibi\WebFOCUS77\basedir\MAKESEQ.fex (The system cannot find the file specified)

Guess I am missing a file or something to use that technique.

I will use the long and complicate one instead hehe.

You just need to create an FSEQ file. It will come in handy to have it.

Got it!

Thank you all for your helps!