Conditional Sum???

[Lucieann]

Is there any way to add a conditional summary field?

In my table, I have three fields that this will involve:

LoanNumber (Can be numeric or alpha-numeric)
Balance (Currency field)
DirectDebt (Currenct field)

If the loan number is a numeric value, I want to add that amount into my subtotal. However I do NOT want to add the balance if the loan number is the same as the previous record. Also some loans numbers with an alpha-numeric value may have data in the amount that I do NOT want to include. I have created the following formula:

If {LOAN1301.LoanNumber}=Previous ({LOAN1301.LoanNumber}) then 0 else
if NumericText ({LOAN1301.LoanNumber}) then {LOAN1301.Balance} else 0

This seems to give me the correct total if I add it in my details section, but I cannot create a summary based on this field.

Clear as mud, right??

 

[paulmarr]

LucieAnne,

What you need here is a running total - put your formula into the Evaluate section - formula - and it will conditionallly summerise your data.

I hope this helps you.

paulmarr

 

[lbass]

Using the running total editor, I think you'd have to use the loan balance field, sum, evaluate based on a formula:

{LOAN1301.LoanNumber}<> Previous ({LOAN1301.LoanNumber}) and
numerictext({LOAN1301.LoanNumber})

Then reset on change of group, assuming you are grouping on account number or something like that.

-LB

 

[paulmarr]

Im pretty sure that is what I suggested.

paulmarr

 

[lbass]

Paul--

I thought your response could be interpreted to mean that she should copy her formula into the running total formula section, which I didn't think would work, which was why I clarified by offering a formula that is slightly different.

-LB

 

[Lucieann]

That works great!! But what could I add to make it include my first record only if it has a numeric loan value?

 

[lbass]

Try:

(Onfirstrecord or
{LOAN1301.LoanNumber}<> Previous ({LOAN1301.LoanNumber})) and numerictext({LOAN1301.LoanNumber})

Be sure to include the parens around the first two lines.

-LB

 

[Lucieann]

That is awesome!! Thank you so much for your help!!