Subtracting amount credit/debit

 

I need to display the total of a sum based on a field value. I basically need to sum all records where a field = "Credit and all those where a field ="Debit" and then subtract the credit total from the debit total to get the overall total. Can you advise how to do this please. i've tried using groups, running totals and formulas but am getting myself in a mess. Any help would be greatly appreciated.

 

Thanks

Esther

//credits

if {table.typefield}='credit' then {table.amountfield}

//debits

if {table.typefield}='debit' then {table.amountfield}

//together

if {table.typefield}='credit then {table.amountfield} else {table.amountfield} *(-1)

 

now you can sum each of these at any grouplevel to get the values you want

 

I have setup three formula fields:

 

credit: if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT}

 

debit: if {SRS_FDU.FDU_CORD}='D' then {SRS_FDU.FDU_AMNT}

 

together: if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1)

 

what do i need to do next?

 

Sorry just to add, there may be multiple credit and debit records, will this still work?

 

Thanks

 

Thanks

if so then you just need to insert a summary as a SUM for each of the formulas at each of the footer levels you want to see results in.

 

This isn't working and i'm now getting myself completely confused!

 

If i've grouped by customer ref where should i be putting the forumals? None of them seem to be calculating correctly.

 

Sorry for being a complete novice!

if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT}

place it on the detail section.

when you preview the report you should see the actual credit amount on every row that is a credit and a zero on all debit rows.

 

to get totals for just credits, click on the sigma sign (the blue E in the toolbar) to insert a summary.

select the "Credits" formual field

calculate as a SUM

Summary location will be group 1 (customer)

this will create a field in the group footer that is the sum of credits for each customer.

If you want a sum for the whole report repeat teh last step put for the summary location select Grand Total (report footer) as the location.

 

Repeat the process for "Debits" using

if {SRS_FDU.FDU_CORD}='D' then {SRS_FDU.FDU_AMNT}

 

finally create the "Together" using

if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1)

when you place this on the detail row you should see the amount on eery row but if it is a credit is is a positive value if it is a debit it is a negative value.

 

 

Any ideas?

 

Thanks

 

what is the field type of SFE_CFEE?

 

Yes that's correct, the SFE_CFEE is a string which i'm guessing is where it's failing.

 

Thanks

//number_FEE

val({SRS_SFE.SFE_CFEE})

 

I assume this value is the same for all rows in the group

you can then insert a summary on this formula field at the group level

maximum({@number_FEE},{group})

 

now in the select criteria you change it to the group select criteria and use the summary conditions here to excldue the whole group frmt he report

maximum({@number_FEE},{group field})=sum({other_formula},{group field})

 

 

Sorry i'm back again!

 

This doesn't seem to be working quite as i would expect it to, I have listed an example below, is anybody able to help me identify where it is going wrong?

 

customer has one credit type{SRS_FDU.FDU_CORD} record of 1,000.00 summing correctly with this forumla :if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT}

 

customer has one debit type{SRS_FDU.FDU_CORD} record of 9,000.00 summing correctly with this formula: if {SRS_FDU.FDU_CORD}='D' then {SRS_FDU.FDU_AMNT}

 

this formula does not seem to be displaying as i would expect:

if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1) -  i would expect to see -8,000.00 but is showing me -9,000.00

 

if i do a sum of this sum then the correct amount is displayed but i am unable to use this to compare with SFE_CFEE as above.

 

any advice?

 

thanks

 

All the fomrula is doing is leaving credits as a positive value and making debits a negative value. So when you go to sum this formula field it will give you the total of all credits - the total of all debits.

if you need to see it change row by row use a Running Total.

right click on Running Total

select new

name='Total" (or whatever)

field to summarize= the formula field above (if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1) )

summary type=sum

evaluate= for each record

reset=never if you want it to keep showing changes for every row or on change of a group if you want to start fresh on a new grouping

 

place it in the detail row to see row by row changes

place it in a footer to see the total only

 

My running total is now displaying the figure as a - but the field i need to compare it to doesn't do this, is there a way to strip this off for the comparison?

 

Thanks

 

 

maximum({@number_FEE},{group field}) + sum({other_formula},{group field}) = 0

 

FDU_AMNT.FDU = Fee amount generated

FDU_CORD.FDU - Credit or Debit? (C or D)

SFU_CFEE.SFU = Expected Fee on Customer Record

 

The fee amount is always a positive number whether credit or debit and a customer may have multiple debits or credits or no credits. I want to sum FDU_AMNT.FDU where the FDU.CORD.FDU is C, sum them again where the FDU_CORD.FDU is D and then take away the total for C from the total for D but leave a positive number despite the figure being a debit. I then want to compare this total to SFU_CFEE.SFU and then only show records where these two figures don't match.

 

Thank you again for all your support, i am completely stumped!

 

Please let me know if you need any further information

you should be able to use the abs() function to foece the value to be positive so try this

 

maximum({@number_FEE},{group field}) = abs(sum({other_formula},{group field}))

 

 

This doesn't seem to work. I have setup a group of customer number, say 12345678 i have then placed the FDU_AMNT in the detail and the FDU_CORD. Customer 12345678 may have a debit record of 500, a credit record of 300 and another debit of 200. I want to be able to sum the debits giving me 700 at the group footer, sum the credits giving me 300 at the group footer then sum the debit minus credit giving me 400. I then want to compare this figure with SFU_CFEE and only display the records where this doesn't match.

 

thanks

 

Thanks

(lets call it "Pos&Neg" for this example) formula field to sum, not the FDU_AMT field. .

 

 

maximum({SRS_SFE.SFE_CFEE},{SRS_SFE.SFE_STUC}) = abs(sum({@Sum of CorD},{SRS_SFE.SFE_STUC}))

 

It's saying a string is required?

 

 

convert it to a number first

call the formula "NumberCheck" (or whatever you want)

 

now us it in the other formula

maximum({@NumberCheck},{SRS_SFE.SFE_STUC}) = abs(sum({@Sum of CorD},{SRS_SFE.SFE_STUC}))

 

A customer who has a debit of 9000 and a credit of 1000 is summing as 10000 rather than 8000, any ideas?

 

here's the formula:

 

//together
if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(1)

 

I am then doing a sum of this though this appears to be doing nothing!

 

thanks

 

 

if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1)

you can use anything you want to display on the detail section (an diffetrnt formual field, the origianl field, etc.) but you have to use the formula with a negative 1 in order to be able to get the correct total.

//together
if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1)

 

when i add the minus to the formula all records are returned.

 

Thanks

First you have to pull all records.

Then you have to get the sum and the maximum for each group.

Then you can suppress or exclude entire groups based on the group values that you get.