Summing a formula field that uses a sum

[Former Member]

I am having a little trouble summarizing a field that represents a calculation of incentives for a group of sales reps.... and I suspect the problem is in the way I arrive at the total for the individual rep.... I cannot use a summary field or a running total on the formula field that renders the individual to provide a total for all reps in the report...

The field is {@total $}, with a value determined thus:

tonumber({@net points} {@rate})*

The fields {@net points} and {@rate})* are arrived at as follows:

NET POINTS:

Sum ({@points}, {SubscriptionTran1.SubSourceID}) - ({@chargebacks})

RATE:

if {SubscriptionTran1.SourceCode} = "DOORCREW" and {@net number} >= 15 then $22.00 else if {SubscriptionTran1.SourceCode} = "DOORCREW" and {@net number} < 15 then $17.00 else

if {SubscriptionTran1.SourceCode} = "KIOSKPRO" and {@net number} >= 15 then $20.00 else if {SubscriptionTran1.SourceCode} = "KIOSKPRO" and {@net number} < 15 then $17.50 else

if {SubscriptionTran1.SourceCode} = "DISTMGR" and {@net number} >= 3 then $25.00 else if {SubscriptionTran1.SourceCode} = "DISTMGR" and {@net number} < 3 then $12.50

In a nutshell, the total commissions (TOTAL $) is a function of the NET POINTS times the effective RATE. The RATE is a function of the type of sale and the number of sales..... and NET POINTS are a function of actual sales activity gleaned from transaction data less any CHARGEBACKS (which are manually assigned to reps through a formula field).

I am sure that I am making a logical error that is preventing summarizing the TOTAL $ at the report level, but I cannot see it. If you have a moment to vet my logic and lend me a hand, it would be greatly appreciated....

Micah

[Former Member · Accepted solution]

Basically, you can't use an aggregate function (e.g., SUM()) on a field that uses an aggregate function.

You need to use a global variable to manually sum the field values. There are several (fairly recent) posts that have examples.

HTH,

Carl