Subtracting One Formula Field from another

Marie Benevento
Marie Benevento used Ask the Experts™
on
In Crystal Reports I have two formulas, one that adds revenue accounts (If {GL_ACCT.ACCT_TYPE}="revenue" then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0) and one that adds expense accounts (If {GL_ACCT.ACCT_TYPE}="expense" then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0).  I am trying to write a formula that will subtract the expense account totals from the revenue account totals.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I think what you really want are 2 formulas like
RevenueOnly
If {GL_ACCT.ACCT_TYPE}="revenue" then
    {GL_BAL.ENDING_BAL}
else
    0

ExpenseOnly
If {GL_ACCT.ACCT_TYPE}="expense" then
    {GL_BAL.ENDING_BAL}
else
    0


You can then use summaries on them to get the totals.

To subtract them

Sum({@RevenueOnly}, {GL_ACCT.ACCT_TYPE}) - Sum({@ExpenseOnly}, {GL_ACCT.ACCT_TYPE})

mlmcc
Marie BeneventoIT Associate

Author

Commented:
I have the two different formulas created.  When I try do to the subtraction I get an error message that says "The field cannot be summarized" with @RevenueOnly highlighted
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Did you use the formulas I provided?

What formulas are you trying to ue?

mlmcc
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

If you use the RevenueOnly formula that mlmcc posted, you should not be getting that error.  However, you need to change the subtraction formula to just:

Sum ({@RevenueOnly}) - Sum ({@ExpenseOnly})

 Put that in the report footer, and you should get the difference between the two account types.  The final formula that mlmcc posted was still trying to get a group total for each of those, which won't work because they're in different groups.  You'd either get one value or the other or neither, depending on the current value of ACCT_TYPE.

 There is an assumption here that ACCT_TYPE is the only group on the report.  If there are other groups, that could change things.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You are correct James.  I was thinking the group was the Account number and they needed group summaries.

mlmcc
Marie BeneventoIT Associate

Author

Commented:
My Groups are
GL_Acct.acct_class_sort
GL_Acct.acct_class
GL_Acct.acct_type

When I change the revenue formula to the way posted above.  It just repeats whatever is the last number in the group.

The subtraction formula posted by James also says the @RevenueOnly cannot be  summarized.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you post your RevenueOnly and ExpenseOnly formulas?

Do you need group totals as well as the grand total?

Where did you put the formulas in the report?

mlmcc
Marie BeneventoIT Associate

Author

Commented:
Yes, the revenue and expense formulas are posted in Group Footer 3.  

I don't need a grand total.  What they are requesting of me is that one account named Tuition Revenue they want me to subtract the expense side from the revenue side.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The formulas if you need to see the values need to be in the details.  Yoou can then right click them and put them where you want.

mlmcc
I think you may have misunderstood something.

 mlmcc asked:

 > Can you post your RevenueOnly and ExpenseOnly formulas?

 You replied:

 > Yes, the revenue and expense formulas are posted in Group Footer 3.

 If that was in response to that question from mlmcc, I believe he was asking if you could post the formulas here, so that we can see them.  And if he wasn't asking for that, then I am.  :-)


 When you say "one account named Tuition Revenue", are you referring to a specific account number?  Does the report include multiple account numbers and you need to get the total revenue - total expense for that one account number?  Is the report grouped by the account?  It doesn't appear to be, unless acct_class_sort or acct_class is something like an account number field.

 James
Marie BeneventoIT Associate

Author

Commented:
Sum Revenue Only formula is:
If {GL_ACCT.ACCT_TYPE}="revenue" then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0

Sum Expense formula is:
If {GL_ACCT.ACCT_TYPE}="expense" then Sum({GL_BAL.ENDING_BAL}, {GL_ACCT.ACCT_TYPE}) else 0

I have attached a screen shot so you can see under Tuition Revenue I have two groups.  The first one is the revenue accounts the second is the expense accounts.  I'm trying to write a formula that subtracts the tuition expense from the tuition revenue.  

The formula that i've been trying is:
If {GL_ACCT.ACCT_CLASS}="tuition revenue" then({@Sum Revenue Only}) - ({@Sum Expense})else 0
that is the set of numbers that is showing between the two accounts. Crystal-Report.docx
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Revenue Only formula should be
If {GL_ACCT.ACCT_TYPE}="revenue" then {GL_BAL.ENDING_BAL} else 0

Expense formula should be
If {GL_ACCT.ACCT_TYPE}="expense" then {GL_BAL.ENDING_BAL} else 0

You can then use this to get the difference

Sum({@Revenue Only},  {GL_ACCT.ACCT_TYPE})  - Sum({@Expense Only},  {GL_ACCT.ACCT_TYPE})  

mlmcc

Marie BeneventoIT Associate

Author

Commented:
When I do the formulas the way you suggest all I get is the last number in the list copied down.  Please see attached. crystal-reports-2.docx
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The number in group footer should be a summary value not the revenue only formula.

If you right click the number in the detail section you can INSERT --> SUMMARY and put it in GF3

mlmcc
Marie BeneventoIT Associate

Author

Commented:
OK, so I inserted the summary into GF3.  Now how do you get the expense portion to subtract from the revenue portion
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can then use this to get the difference

Sum({@Revenue Only},  {GL_ACCT.ACCT_TYPE})  - Sum({@Expense Only},  {GL_ACCT.ACCT_TYPE})  

mlmcc
No, mlmcc.  You're forgetting that the two formulas are producing values for different ACCT_TYPE's, so you're trying to subtract the total from one ACCT_TYPE group from the total for a different ACCT_TYPE group, and ACCT_TYPE can only have one value at any given time (so, at most, you'll only have one of those totals available when that formula is evaluated).

 I'm thinking that he'll need to use a formula in the ACCT_TYPE group footer to save the revenue and expense group totals in variables and then use the variables to get the difference at the end.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You are right.  

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
How about this formula to get the difference

If {GL_ACCT.ACCT_TYPE}="revenue" then
    {GL_BAL.ENDING_BAL}
else
    -{GL_BAL.ENDING_BAL}

Now  just summarize that formula for the difference between expense and revenue.

mlmcc


Oo.  Nice.  But if there could be other account types, he'd need to check for expense too.

If {GL_ACCT.ACCT_TYPE}="revenue" then
    {GL_BAL.ENDING_BAL}
else
  if {GL_ACCT.ACCT_TYPE}="expense" then
    -{GL_BAL.ENDING_BAL}


 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Good point.

mlmcc
Marie BeneventoIT Associate

Author

Commented:
Ok so I built the built the formula James0628 suggested.  
went to GF3 and inserted a summary chose the formula as the field I wanted to summarize.  Left Calculate this summary as sum.  But it is not subtracting the tuition expense from the tuition revenue.  What did I do wrong?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is GF3 the correct group?
For this to work G3 has to be above the revenue and Expense groups.
ie tutition revenue and tuition expense must be group 4

mlmcc
Marie BeneventoIT Associate

Author

Commented:
I moved it to GF2 so it is above the revenue and expense groups but it adds the two totals together.  It is not subtracting them
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Just summarize the new formula.  or are the values for expense already negative?

mlmcc
Did you include the "-" on the last line?  Just checking.

 I think mlmcc may be right about the expense amounts already being negative.  His idea was that if it's a revenue account, the formula outputs ENDING_BAL, and if it's an expense account, the formula outputs negative ENDING_BAL.  For any other ACCT_TYPE, the formula just produces 0.  So, for example, if you had 10 revenue and 3 expense (and some other account types), you'd get 10 and -3, and when you summed those, you'd get 7.  But if the expense amounts are already negative, then you don't want to reverse them.  If that was the case, you could change the formula to:

If {GL_ACCT.ACCT_TYPE}="revenue" or {GL_ACCT.ACCT_TYPE}="expense" then
    {GL_BAL.ENDING_BAL}


 James
Marie BeneventoIT Associate

Author

Commented:
Thank you gentleman for all your help.  The calculation is working.
You're welcome.  I must say, I really like that last formula that mlmcc came up with.  I was thinking that you were going to have to use a variable to accumulate a total from the different ACCT_TYPE groups, but his formula avoids that and lets you just do a simple summary.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial