Calulating the Average 1

[dannyb29]

Good Afternoon all.
Would you be able to help me with what should be a simple formula.

I am trying to divide the results of two fomulas to calculate an average to then place in a crosstab.

Formula 1 - This is a DstinctCount in the crosstab
if {AVBRecordTypes.Description}= "Delivery Acceptance from PDA" then
{AVBTransactionLogPd.TxnId} else
tonumber ({@null})


Formula 2 - This is summarised as MAX in the crosstab
DistinctCount ({AVBTransactionLogPd.ActionDate},{Users.UserId})

In my mind the formaula should be
Formula 1/ Formula 2

but this is does not work


DannyB

 

[lbass]

Create a formula {@0}:

whilereadingrecords;
0

Add this as your third summary field. Then select the {@Formula1} summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar txn := currentfieldvalue;
false

Repeat for {@Formula2} but enter:

whileprintingrecords;
numbervar adt := currentfieldvalue;
false

Then select the {@0} summary->right click->format field->DISPLAY STRING and enter:

whileprintingrecords;
numbervar txn;
numbervar adt;
if adt=0 then
"--" else
totext(txn/adt,2) //2 for two decimals

-LB

 

[dannyb29]

Hi IBass
This part of the formula is not working

whileprintingrecords;numbervar txn;numbervar adt;if adt=0 then"--" elsetotext(txn/adt,2) //2 for two decimals

It is throwing up an error on this section of the formula
elsetotext(txn/adt,2) //2 for two decimals

Sorry to be a pain.

Dannyb

 

[lbass]

Don't let the words run together--add a return after "else".

-LB

 

[dannyb29]

Many Thanks! Worked a treat!