Former Member
Apr 23, 2010 at 04:09 PM

Summing a formula field that uses a sum

17 Views

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