Subtracting Accounts

[Jason2121]

Hello

I am trying to find the difference between Account Receivables and Account Payables for a Consolidated Bal Sheet.

Acct Rec Accts
If {@Account1} = "1.1190.99" or {@Account1} = "1.1180.22" or {@Account1} = "1.1190.77"
or {@Account1} = "0.1190.9" Then ({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL})

Acct Payable Accts
If {@Account1} = "1.4010.1" or {@Account1} in "0.4013.9" to "0.4016.5" or {@Account1} in "0.4017.1"
to "0.4017.4" or {@Account1} = "1.4040" Then -({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL})

Thank you for any help

 

[fisheromacse]

Did you try using variables?
Something like this...
Setup variables for received, for payable and for difference.

numbervar rec;
numbervar pay;
numbervar diff;

If {@Account1} = "1.1190.99" or {@Account1} = "1.1180.22" or {@Account1} = "1.1190.77"
or {@Account1} = "0.1190.9" Then rec := ({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL});

If {@Account1} = "1.4010.1" or {@Account1} in "0.4013.9" to "0.4016.5" or {@Account1} in "0.4017.1"
to "0.4017.4" or {@Account1} = "1.4040" Then pay := ({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL});

IF not(isnull(rec)) and not(isnull(pay))
THEN diff := rec-pay;

diff;

 

[Jason2121]

I created two formulas, one for AP and one for AR.
shared numbervar apAmt;

If {RV_GLSA_ROLLING_BAL.GL_YR} = {@Current Year}
Then
If {@month} = 10
Then
If {@Account1} = "1.4010.1" or
{@Account1} in "0.4013.9" to "0.4016.5" or
{@Account1} in "0.4017.1" to "0.4017.4" or
{@Account1} = "1.4040"
Then
apAmt := apAmt + -({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL})

I then created a third formula and put it in the RF.
shared numbervar apAmt;
shared numbervar arAmt;

apAmt + arAmt

I was able to get the differences but now I have a formula and would like to have the difference taken from Account 1.1190.99

If {RV_GLSA_ROLLING_BAL.GL_YR} = {@Current Year} Then

If {@month} = 10 Then

If ({@Account1} = "1.1190.99") then -({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL} + {@Elim 5}) else
if ({@Account1} = "1.1180.22" or {@Account1} = "1.1190.77" or {@Account1} = "0.1190.9" or {@Account1} = "0.1401"
or {@Account1} = "1.4010.1" or {@Account1} in "0.4013.9" to "0.4016.5" or {@Account1} = "0.4017.1" to "0.4017.4"
or {@Account1} = "1.4040" or {@Account1} = "1.4510" or {@Account1} = "1.4550.1") then -({RV_GLSA_ROLLING_BAL.GL_PER_10_ACCUM_BAL})

The formula works but I get an error
"A summary has been specified on a non-recurring field - Details: @Consolidated"
Consolidated is another formula to get the total amounts after the eliminatign enteries = See brief example.
Column 1 - Amount Current
C2 - Elim Entries (Elim 2 formula)
C3 - Consolidated Amount (Consolidated formeula)

{@Amount Current}+ {@Elim 2}
Thanks for your help