Hello,

How do I sum a column of numbers only if the number is say a negative number? Currently I have the sum of a column working except it is summing all numbers and I only want to sum the negative numbers.

I want to sum the balance

Here is the code I am currently using:
TABLE FILE EMAILRECAP
SUM
'EMAILRECAP.EMAILREC.balance'
BY 'EMAILRECAP.EMAILREC.regionAbrev' AS 'Region Abrev'
ACROSS 'EMAILRECAP.EMAILREC.day' AS 'Day'
HEADING
"CSR RECAP"
"&DATEtrMDYY <+0> &TOD "
" "
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'

Thanks for the help in advance.
Kelly

Can you just define a field that only includes negative numbers and then sum that defined field?

DEFINE FILE EMAILRECAP
NEGBALANCE/D12.2 = IF balance LT 0 THEN balance ELSE 0;
END

TABLE FILE EMAILRECAP
SUM NEWBALANCE
BY .....
END

Another way without using a define would be to just filter out the positive balance records by adding IF balance LT 0 to your table request.

Thanks for the suggestions. I tried the define but I don't know how to get it into the report like I want it.

ET I don't want to filter the data out. I want to show all numbers but have a sum of only the negative numbers. Here is what I would like the data to look like:


CB 170 -59 -59 -59
CW 185 -17 -17 -17
GS 118 -64 -64 -64
HL 209 15 15 15
HO 163 -51 -51 -51
NC 304 70 70 70
NE 118 -106 -106 -106
NW 282 49 49 49
PS 186 -61 -61 -61
SE 187 -58 -58 -58
SW 247 45 45 45
UM 152 -41 -41 -41
TOTAL 2321 -278 -278 -278

So I want the totals to be 0 -457 -457 -457

This is just test data that is why the numbers are coming back the same.

KellyT, I like RECAP:

TABLE FILE ????
SUM
COMPUTE
COL1/P13.2C = IF COLA LT 0 THEN COLA ELSE 0; NOPRINT
COMPUTE
COL2P13.2C = IF COLB LT 0 THEN COLB ELSE 0; NOPRINT
COMPUTE
COL3/P13.2C = IF COLC LT 0 THEN COLC ELSE 0; NOPRINT
COMPUTE
COL4/P13.2C = IF COLD LT 0 THEN COLD ELSE 0; NOPRINT
BY ????
BY ????
ON SORT_COLUMN RECAP
COL_01/P13.2C = IF COL1 LT 0 THEN COL1 ELSE 0;
COL_02/P13.2C = IF COL2 LT 0 THEN COL2 ELSE 0;
COL_03/P13.2C = IF COL3 LT 0 THEN COL3 ELSE 0;
COL_04/P13.2C = IF COL4 LT 0 THEN COL4 ELSE 0;
ON SORT_COLUMN SUBFOOT
" "
"TOTAL for School -SCHOOL : -COL_01-COL_02-COL_03-COL_24-COL_05"

- Dash = Left Caret <


Then STYLESHHET:

TYPE=SUBFOOT, LINE=2, ITEM=1, JUSTIFY=LEFT , STYLE=BOLD, SIZE=8, $
TYPE=SUBFOOT, LINE=2, ITEM=2, JUSTIFY=LEFT , STYLE=BOLD, SIZE=8, $
TYPE=SUBFOOT, LINE=2, ITEM=3, JUSTIFY=LEFT , STYLE=BOLD, SIZE=8, $
TYPE=SUBFOOT, LINE=2, ITEM=4, STYLE=BOLD, SIZE=8, POSITION = COLA,$
TYPE=SUBFOOT, LINE=2, ITEM=5, STYLE=BOLD, SIZE=8, POSITION = COLB,$
TYPE=SUBFOOT, LINE=2, ITEM=6, STYLE=BOLD, SIZE=8, POSITION = COLC,$
TYPE=SUBFOOT, LINE=2, ITEM=7, STYLE=BOLD, SIZE=8, POSITION = COLD,$
TYPE=SUBFOOT, LINE=2, ITEM=7, STYLE=BOLD, SIZE=8, POSITION = COLD, COLOR=RED,WHEN=COLD LT 0,$
TYPE=SUBFOOT,
LINE=2,
BACKCOLOR=LIGHT BLUE,
$

Tom

This message has been edited. Last edited by: Tom Flynn,

Thanks for the help but I think I probably stated the problem wrong. It worked how I said when I did the were if balance < 0.

How do I create a hold file on a total column? I created the hold file fine but then when I go to create a new report from it the total isn't a field that I can use because it was a formula field when creating the hold. I hope that makes sense.

Then from that total field I'll be able to figure the grand total.

Thanks for all the help.

See lower case inset in your code. This will give day colum total then do you need the across? You may have to rearrange some sorting to get your hold, but you could do a where total on the first sum then.

quote:

TABLE FILE EMAILRECAP
sum 'emailrecap.emailrec.balance'
by 'emailrecap.emailrec.day'
SUM
'EMAILRECAP.EMAILREC.balance'
BY 'EMAILRECAP.EMAILREC.regionAbrev' AS 'Region Abrev'
ACROSS 'EMAILRECAP.EMAILREC.day' AS 'Day'
HEADING
"CSR RECAP"
"&DATEtrMDYY <+0> &TOD "
" "
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'