countif? 1

[celauritz]

I wrote a report a long time ago using a distinct count of work orders. In the details section I wrote the formula:

if {orders.servicecode} = "BAS" then {orders.workordnumber} else 0

I then wrote a formula in the group field for the distinct count of work order numbers minus one to get the total.

However, if a work order contained two of the service BAS, it would be doubled in the details section. However, I cannot get an actual count because the count function counts all the zeroes, telling me basically how many entries are in the orders database. Is there a way to count only the numbers greater than zero?

 

[lbass]

One way of doing this is to create a formula {@null} by opening a new formula and saving and closing it without entering anything. Then change your formula to:

if {orders.servicecode} = "BAS" then {orders.workordnumber} else tonumber({@null})

Then you can insert a distinctcount on this and get the correct result, since nulls don't get counted.

The subtract 1 approach doesn't really work because if all records met your criteria, your result would be off by 1.

The other way of handling this is by using running totals where you use the criterion in an evaluation formula, but inserted summareis are faster.

-LB

 

[synapsevampire]

Sure, and you can compare previous records too.

Hard to know what you want, you state "I then wrote a formula in the group field"without telling which is the groupfield, why is that???

Anyway, you should post example data and expected output.

Try something like:

Group header:
whileprintingrecords;
numbervar CurrCount:=0;

Details:
whileprintingrecords;
numbervar CurrCount;
if {orders.servicecode} = "BAS"
and
{table.workorder} <> previous({table.workorder}) then
CurrCount:=Currcount+1;

Then in the group footer use:
whileprintingrecords;
numbervar CurrCount;
Nunmbervar TotCount;
Totcount:=Totcount+CurrCount;
Currcount

Then you can display the count in the report footer:

whileprintingrecords;
Nunmbervar TotCount

-k

 

[celauritz]

Thanks, I never realized that about the -1 approach. Worked great.