Total Divided by Distinct Count 1

[GC2000]

Well I am not even sure if this can be done. I have a mock crosstab that currently looks like this:

Service 1 Service 2 Service 3
Completed
Opened
Average

This is in the Report Header and is based on summaries of formula's. I have the same thing mock crosstab in the Group header which is grouped on agents.

For the Average I am trying to divide the Completed for each service by the number of Agents that had something completed for that service. For instance there are 74 orders completed for Service 1 and 11 total agents but only 4 of them did Service 1 work. The average should be 18.5 (74 / 4). I can get this in the footer using a running total but I need it in the header. I have tried using variables but they also do not work in the header. Does anyone have any idea if this can even be done?

Thanks

 

[lbass]

Create a formula {@null} by opening and saving a formula without entering anything. Then create formulas (one for each service) like this:

//{@svc1agts}:
if {table.svc} = 1 then
{table.agentID} else
tonumber({@null})

If agentID is a string or if you use the agent's name, remove the tonumber(). Then reference this formula in your average by using:

distinctcount({@svc1agts})

-LB

 

[GC2000]

That worked perfectly. Thanks for the assistance.