I want to produce totals for all rows using only a few selected columns.

When I use COMPUTE after ACROSS my output shows the 'NET SALES' row description with the corresponding reporting period values.

A new column 'LTM' is displayed on the extreme right.

The next row, and all others following, are missing the row descriptions, ie EBIT, EBITDA, etc. Corresponding values are displayed, but offset one column to the left. Also, the totals are not coming through for the 'LTM' column, except for the last row and even then I cannot reconcile the total shown.

When I remove the COMPUTE phrase entirely, all row descriptions are displayed with values lined up appropriately.

Does anyone know why suppression of row descriptions is happening? My code is shown below.


TABLE FILE HOLD5
SUM
NETSALESA AS 'NET SALES' OVER
EBIT AS 'EBIT' OVER
EBITDA AS 'EBITDA' OVER
*** etc ***
RETURN_EQTY AS 'RETURN ON EQUITY'
ACROSS FINDATADATE AS ' '
COMPUTE LTM/P9CB=C2 + C3; AS 'LTM'
WHERE STATEDATEYR EQ CURRYR OR STATEDATEYR EQ LASTYR OR STATEDATEYR EQ LASTYR2 OR STATEDATEYR EQ LASTYR3 OR STATEDATEYR EQ LASTYR4 OR STATEDATEYR EQ LASTYR5
WHERE NAMEU CONTAINS '&AWCOMPANY'
ON TABLE SET ONLINE-FMT PDF
*** etc ***

Hopefully at a glance someone can see what is happening here. I'm using version 5.2.4. Thanks, vickie

What is LTM supposed to represent in this case?

LTM, the COMPUTE value, is short for 'last twelve months'. My report details quarterly earnings for a particular company, showing quarterly periods across with financial data items top to bottom. I want to add the values in 4 columns, representing total net sales, etc, of the past 4 quarters. This 12 month total may or may not represent fiscal year numbers, depending on the most recent quarter I am reporting on.

Vickie,

I do not know of any reason why this would be happening, unless of course, there is a problem with referencing columns in a COMPUTE when using OVER. Every conceivable thing I could think of when testing would not work.

However, there is a solution that would definitely work for you, the McGyver technique.

I quickly mocked up one using some of my own financial data at my location and it worked great.

When creating your master, you should use the number of categores as the OCCURS. In this example, 3 works because of the three categories identified (NETSALESA, EBIT, EBITDA). You should increase accordingly, depending on the categories.

FILEDEF FINANCE DISK finance.mas
-RUN

-WRITE FINANCE FILE=FILENAME, SUFFIX=FIX
-WRITE FINANCE SEGNAME=ONE,SEGTYPE=S1,$
-WRITE FINANCE FIELD=BLANK,ALIAS=BLANK,A1,A1,$
-WRITE FINANCE SEGNAME=TWO, PARENT=ONE, OCCURS=4,SEGTYPE=S0,$
-WRITE FINANCE FIELD=FILL,ALIAS=FILL,A1,A1,$
-WRITE FINANCE FIELD=CTR,ALIAS=ORDER,I2,I4,$
-CLOSE FINANCE

Assuming that your first hold file containing your financial data is STEP1, the following DEFINE will work for you:

DEFINE FILE STEP1
CATEGORY/A9 = IF CTR EQ 1 THEN 'NET SALES' ELSE
IF CTR EQ 2 THEN 'EBIT' ELSE
IF CTR EQ 3 THEN 'EBITDA' ELSE
FIN_DATA/format MISSING ON = IF CTR EQ 1 THEN NETSALESA ELSE
IF CTR EQ 2 THEN EBIT ELSE
IF CTR EQ 3 THEN EBITDA ELSE MISSING ;
END

TABLE FILE STEP1
SUM FIN_DATA
BY CATEGORY
ACROSS FINDATADATE AS ' '
COMPUTE LTM/P9CB=C2 + C3; AS 'LTM'
...
END

Ken

Thank you, Ken. I will try the McGyer technique. I've seen this mentioned. Wasn't sure just how or when to use it.

I appreciate the detailed code, it will certainly help me! vickie

Vickie,

One thing I failed to mention was that in the creation of the hold file STEP1 (which contains all of your data), you need to include a joinkey field, such as

COMPUTE JOINFLD/A1 = 'F' ;

this will be used in the join.

Will do, Ken...thanks, vickie