Conditional average

[NN10473]

Hello all,

New to Crystal reports and this forum, but feeling very optimistic ;-)

I have a report that shows the number of trips performed by each driver for the selected date range. This is established via a cross tab. Very simple report, list of drivers in the first column and each day in the range following.

I am having a problem creating a formula that will show the average of trips for each driver for the established range, but taking into consideration only the days that the driver actually worked.....
As an example, lets say our range is Monday to Friday (5 days) and the total trips for driver A is 15. This driver only worked Monday, Wednesday and Friday so his average should be 15/3=5 and not the standard 15/5=3 that the computer would assume.....

Any suggestions?

Thanking you all in advance.....

 

[Madawc]

You need a formula field to test, something like

If {worked.flag} = "Y"
then 1
else 0

If you now sum @workdays, you'd get 3 rather than 5 in your example.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524. It will find averages for you, without the need to code them in detail.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP