Hi Everyone

I am trying to do something that is giving me some grief. Here is a sample using the EMPLOYEE table...

TABLE FILE EMPLOYEE
SUM CURR_SAL ED_HRS
BY BANK_NAME BY DEPARTMENT ACROSS JOB_DESC
COMPUTE DUMMY1/D12.2M=CURR_SAL/2; AS '50% OF CUR_SAL'
COMPUTE DUMMY2/F6.2=ED_HRS/2; AS '50% OF ED_HRS'
ON BANK_NAME SUBTOTAL AVE. CURR_SAL AS 'AVE'
ON BANK_NAME SUBTOTAL AVE. ED_HRS
WHERE JOB_DESC EQ 'PROGRAMMER TRAINEE';
ON TABLE NOTOTAL
END

If you run that you will see that the Average values for the computed cols Dummy1 and Dummy2 are wrong. In fact it is summing the values and not averaging them.

So how do I get the average of those 2 computed fields? I have tried a ton of variations using RECAP and SUMMARIZE and I am not getting it. Seems like it would be so easy.

THX in advance.

Jodye

Jodye,

The following works:

TABLE FILE EMPLOYEE
SUM CURR_SAL ED_HRS
COMPUTE DUMMY1/D12.2M=CURR_SAL/2; AS '50% OF CUR_SAL'
COMPUTE DUMMY2/F6.2=ED_HRS/2; AS '50% OF ED_HRS'
BY BANK_NAME BY DEPARTMENT ACROSS JOB_DESC
ON BANK_NAME SUBTOTAL AVE. CURR_SAL AS 'AVE'
ON BANK_NAME SUBTOTAL AVE. ED_HRS
ON BANK_NAME SUBTOTAL AVE. DUMMY1
ON BANK_NAME SUBTOTAL AVE. DUMMY2
WHERE JOB_DESC EQ 'PROGRAMMER TRAINEE';
ON TABLE NOTOTAL
END

In this, the two computes for DUMMY1 and DUMMY2 have been moved before the ACROSS. Therefore you can use them in the SUBTOTALs. I question why you are using ACROSS in this case. If you are just using a single JOB_DESC, then you do not need to use ACROSS.

That being said, I suspect that you want to go across multiple values and that you just included one JOB_DESC as an example. Is this correct? If so, then the sequence command comes in handy as indicated below:

TABLE FILE EMPLOYEE

SUM COMPUTE DUMMY1/D12.2M=CURR_SAL/2; AS '50% OF CUR_SAL'
COMPUTE DUMMY2/F6.2=ED_HRS/2; AS '50% OF ED_HRS'
BY BANK_NAME
BY DEPARTMENT
SUM CURR_SAL
ED_HRS
BY BANK_NAME
BY DEPARTMENT
ACROSS JOB_DESC AS ''
ON BANK_NAME SUBTOTAL AVE. CURR_SAL AS 'AVE'
ON BANK_NAME SUBTOTAL AVE. ED_HRS
ON BANK_NAME SUBTOTAL AVE. DUMMY1
ON BANK_NAME SUBTOTAL AVE. DUMMY2
WHERE JOB_DESC EQ 'PROGRAMMER TRAINEE';
ON TABLE NOTOTAL
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=BANK_NAME, SEQUENCE=1,$
TYPE=REPORT, COLUMN=DEPARTMENT, SEQUENCE=2,$
TYPE=REPORT, COLUMN=DUMMY1, SEQUENCE=98, $
TYPE=REPORT, COLUMN=DUMMY2, SEQUENCE=99, $
ENDSTYLE
END

Ken

I like ken's answer
but here's what is making me nuts...i've not seen this behaviour before
SUBTOTAL AVE.fieldname gives an error
but
SUBTOTAL AVE. {blank space} fieldname
works.

here's noreen's article on that blank space

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

Hi Ken and Sue

Ken your example works perfectly. It never occurred to me to move the computes before the across. And yes, the code I posted is a very simplified example of what I was trying to do.

Sue: I have seen that crazy AVE. blank space before. It is documented somewhere IIRC.

Thanks guys

Jodye