Dividing Two formulas for Avg

[jlr123]

Need Average Sale Amount of each customer by Salesperson:
This is my YYD Summary for each salesperson
if {AR_CustomerSalesHistory.FiscalYear} = {?Fiscal Year} and {AR_CustomerSalesHistory.FiscalPeriod} in {?Fiscal Period} then {AR_CustomerSalesHistory.DollarsSold} else 0
This is number of Accounts with Sales
DistinctCount ({AR_Customer.CustomerNo}, {AR_Customer.SalespersonNo}) - {#CustomerCountNoYTDSales}

Thanks for your assistance.

 

[lbass]

How is your running total set up exactly?

-LB

 

[jlr123]

Only one Running Total of No YTD Sales:
Field to Summarize: AR_Customer. Customer No
Type of Summary: distinct Count
Formula: Sum ({@YTD}, {AR_Customer.CustomerNo})=0
On change of Group: Group #1 Ar_Customer.Salesperson
jlr123

 

[lbass]

Instead of using the running total, do this.

First, make a formula {@null} by creating a new formula that contains nothing and saving it. Then create a second formula:


//{@CustYTD>0}:
if {@YTD} > 0 then
{AR_Customer.CustomerNo} else
{@null}

Then use the following for your summary:

Distinctcount({@CustYTD>0},{AR_Customer.SalespersonNo})

-LB

 

[jlr123]

I don't see the average sale formula.
jlr123

 

[lbass]

Sum({@YTD},{AR_Customer.SalesPersonNo}/distinctcount({@CustYD>0},{AR_Customer.SalespersonNo}

-LB

 

[jlr123]

ok, I am having a problem. What is the @null formula please?

 

[lbass]

There is NOTHING in the formula. Open the formula the way you would to create a new formula. Then save it without entering anything. Name it "Null".

-LB

 

[jlr123]

For some reason, Crystal does not like this formula
Distinctcount({@CustYTD>0},{AR_Customer.SalespersonNo})


Crystal pop up says {CustYTD>0} field not known.

I named the formula CustYTD and copied and pasted it as you have it in the thread.

When I change the formula to Distinctcount({@CustYTD},{AR_Customer.SalespersonNo})
the result is +1 more than it should be.

 

[lbass]

If you named it CustYTD then use that formula instead of CustYTTD>0.

-LB