Columns for negative and positive numbers

[Dukester0122]

I need a formula to show one field in two columns. The left column is supposed to show the field in negative and the right column positive. My field is IV0001.

 

[Madawc]

Do two formula fields, one for each column. One shows negatives and the other shows positives (and zero, if you want zero.

Madawc Williams
East Anglia
Great Britain

 

[Ngolem]

You could do it in one formula

@display_PosNeg

WhilePrintingRecords;

If {Table.number} >= 0 then
ToText({table.number},y) //where y is no. of decimals you want
else
//If you want to show the number as a positive then
//take the ABS({table.number})...x is some number of
//spaces to move the neg numbers to the right.
Spaces(x) + ToText({table.number},y) ;

Jim Broadbent

 

[Dukester0122]

I haven't used Crystal for awhile so bear with me. I tried this formula but didn't work:

If {Table.number} >= 0 then ToText({table.number},y)
else ABS({table.number})
then Spaces(x) + ToText({table.number},y) ;

Am I missing anything?

 

[synapsevampire]

Since the field might be negative or positive, I assume that you'll want to explicitly make sure that one is positive, and one is negative.

Create 2 formulas (Insert->Field Object->Right Click Formula Fields->New):

@PosNum
abs({Table.number})

@NegNum
abs({Table.number})*-1

Now place those formulas on the report as you would a table field.

-k

 

[synapsevampire]

BTW, not sure what "My field is IV0001" meant...

Is that the name of the field? If so, replace the above

({Table.number})

with your table and field name.

-k

 

[Ngolem]

you have used the essence of my formula but copied it wrong

@display_PosNeg

WhilePrintingRecords;

If {Table.number} >= 0 then
ToText({table.number},y) //where y is no. of decimals you want
else
//If you want to show the number as a positive then
//take the ABS({table.number})...x is some number of
//spaces to move the neg numbers to the right.
Spaces(x) + ToText({table.number},y) ;

These are only guidelines youhave to replace the values with real ones


{Table.number} has to be replaced with the real field value
x has to be replaced with a number such as 20
y has to be replaced with a number such as 2 if you want 2 decimals....

// means comments...you should read them


Jim Broadbent

 

[mbarron]

I'm assuming you may want to do some sort of summary on the columns. If that's the case I would do as Madawc suyggests and create two formula. The formula that Ngolem suggests reslults in a string.

For positive (and 0):
If {table.field}>=0 then {table.field}

For negative:
If {table.field}<0 then {table.field}

Then suppress the fields.
For the positive field:
If {table.field}<0 then true else false

For the negative:
If {table.field}>=0 then ture else false




Mike

 

[Dukester0122]

I did replace the fields with my tables and I did get the result but this is not what I wanted.

Mike,

Thanks a lot. It looked exactly as I wanted it.

 

[synapsevampire]

Admittedly, the requirements sounded fishy, but how did you come up with them wanting a summary from that post, Mike, yer psychic!

-k

 

[Dukester0122]

Sometimes you don't have to make things complicated. My question was very simple. One field, two results - on the left is anything that's positive and on the right is all negative.

Anyway, you're post might also help me in the future. Tks.

 

[Ngolem]

***********
Original Post

I need a formula to show one field in two columns. The left column is supposed to show the field in negative and the right column positive. My field is IV0001.
**************

My solution works fine for the stated problem. Yes it is a staggered string but the specific request was for ONE formula to split a field value into positive and negative....I accomplished all of that.

No mention of summary procesing here....

{mutter}...{mutter}...where in the Hell is that Crystal Ball!!!

Jim Broadbent