Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Total Divided by Distinct Count 1

Status
Not open for further replies.

GC2000

Programmer
Sep 19, 2008
27
CA
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
 
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
 
That worked perfectly. Thanks for the assistance.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top