Need help creating a count summary on a formula field

[mattner]

I have a formula field that indicates whether or not the customer has a past due balance. I need to get a COUNT of all the past due accounts from this formula field.

When I try creating the formula, I receive the error "The summary/running total field could not be created"

I am using the count function (Count[fld, condFld]) in the formula editor to create this.

It is Crytal Reports Version 8.5

Any ideas?

 

[MJRBIM]

First, create a new formula in your detail section....

NAME : PastDueBinary

FORMULA :IF {@YourPastDueFormula} = "Y" THEN 1 ELSE 0

Then use the Insert -> Grand Total tool to total {@PastDueBinary}

 

[mattner]

I tried this but the Insert Subtotal, Grand Total, and Summary options are greyed out.

 

[MJRBIM]

The binary formula solution works OK on one of my reports to count a similar formula - the problem may be with the output from your PAST DUE FORMULA.

Please post your PAST DUE FORMULA and/or some sample rows of data with Headers.

 

[mattner]

My past due formula is:

if Maximum ({@Days past due}, {ARMAST.CUSTNO})>0 then "Yes"

The @Days past due formula is :

datadate - ({ARMAST.INVDTE}+{ARMAST.PNET}). Basically, todays date minus the invoice date plus the terms.

 

[MJRBIM]

Insert these formulas in thes specified areas -

REPORT HEADER

Name : {@ZeroCounterReset}
Formula: Shared numberVar ZeroCheck := 0;

DETAIL


Name : {@Days Past Due}
Formula: datadate - ({ARMAST.INVDTE}+{ARMAST.PNET})


Name : {@PastDueCustomerNo}
Formula: IF (DataDate - ({ARMAST.INVDTE}+{ARMAST.PNET})) >0 THEN {ARMAST.CUSTNO} ELSE 0


Name : {@ZeroCheckAdd}
Formula: Shared numberVar ZeroCheck;
IF (ZeroCheck = 0 and {@PastDueCustomerNo} = 0) THEN ZeroCheck := 1 ELSE ZeroCheck := ZeroCheck;

Report Footer

Name : {@PastDueCustomerCount}
Formula: Shared numberVar ZeroCheck;
DistinctCount ({@PastDueCustomerNo}) - ZeroCheck;

Let me know how it works...

 

[mattner]

I believe this worked corectly showing me the grand total number of past due accounts. Now to add to the confusion I have this report grouped by:

#1 - Division
#2 - Collector Name

I need to subtotal past due accounts by both groups as well as the grand total.

Thaks for all your help MJRBIM!!!!!!

 

[MJRBIM]

It's the same process for each grouping - declare the variable as 0 at the top of the group, check for any non-past-due records and the subtract from the distinct count in the group footer.

GROUP 1 HEADER

Name : {@ZeroGroup1CounterReset}
Formula: Shared numberVar ZeroGroup1Check := 0;

DETAIL

Name : {@ZeroGroup1CheckAdd}
Formula: Shared numberVar ZeroGroup1Check;
IF (ZeroGroup1Check = 0 and {@PastDueCustomerNo} = 0) THEN ZeroGroup1Check := 1 ELSE ZeroGroup1Check := ZeroGroup1Check;

Group 1 Footer

Name : {@PastDueCustomerCount}
Formula: Shared numberVar ZeroGroup1Check;
DistinctCount ({@PastDueCustomerNo}, {ARMAST.Division}) - ZeroGroup1Check;

You will need to create these formulas with different names (ie. ZeroGroup2Check) for each group...

 

[mattner]

NICE!!!! It all works perfectly. Thanks so much for the help.