Debit – Credit SQL Formula
I am trying to write what I thought would be a pretty simple formula for a transaction search
{debitamount}-{creditamount}
It is just coming up blank.
I am trying to get both invoices and journals onto a single search and do a summary total, which I cannot do using the transaction total since that doesn’t exist for a journal.
Anyone know a trick to get this to work?
I’d also like to get this to work in an Analytics dataset as well.
Formula currency = CASE WHEN {debitfxamount} is NULL THEN 0 ELSE {debitfxamount} END)-(CASE WHEN {creditfxamount} is NULL THEN 0 ELSE {creditfxamount} END)
Or you can use
Formula currency = nvl({debitfxamount},0) – nvl({creditfxamount},0)
Neither of those is working for me. I’m not sure what the issue is. I have values in those fields.
this is the error I’m getting in analytics
You have syntax error in your formula (Unsupported element: syntax error, state:598 at the end of formula). Please see the documentation for supported syntax. (Error on line 1 column 137.)
Sorry I don’t see it’s for dataset
coalesce({debitfxamount},0) – coalesce({creditfxamount},0)
TO_NUMBER({transactionlines.debitforeignamount})-TO_NUMBER({transactionlines.creditforeignamount})
It’s not very clear what you want
The totalamount for the JE is always 0 because debit=credit
I think you need to compare only total amount with credit amount or debit amount
I am trying to a line total for journals and invoices. not a total transaction total. I am working on either a saved search or a dataset, whichever one is actually going to work
Is there a way to paste a screenshot in here?
a line item on a journal looks like this
Debit 100 Credit 0
Debit 0 Credit 100
I want a column that will show
debit – credit = 100
debit – credit = -100
Then I can put a summary on the formula to get the 0
I was able to get this to work in a dataset
CASE WHEN {transactionlines.accountingimpact.debit#currency_consolidated} is NULL THEN 0 ELSE {transactionlines.accountingimpact.debit#currency_consolidated} END)-(CASE WHEN {transactionlines.accountingimpact.credit#currency_consolidated} is NULL THEN 0 ELSE {transactionlines.accountingimpact.credit#currency_consolidated} END
Thanks for the help!
