Conditional formula to sum values depending on values from a column

[ekamber]

I hope someone call help me with this.

I have a number of columns and the main columns of interest at the moment are 'IsBid (bool)' and 'Vol (integer)'.

I want to create a count of number of records where 'IsBid=false'.
Also I need the total/average of 'Vol' where 'IsBid=false'.

For example:
IsBid Vol
----- ----
T 10
T 5
F 1
F 150
F 15

So, the first part of a count of number of records where 'IsBid=false' should return 3.
The second part of the total/average of 'Vol' where 'IsBid=false' should return 166/55.33 respectively.

Any help is apprecaiated.

Thanks,

Emir

 

[lbass]

Use two formulas:

//{@countF}:
if {table.IsBid} = false then 1

//{@volF}:
if {table.IsBid} = false then {table.vol}

Then create a formula {@aveF}:
sum({@volF},{table.group})/sum({@countF},{table.group})

If you are doing this at the report level, then use:
sum({@volF})/sum({@countF})

-LB

 

[e11a]

This is a long winded way to do this and someone else probably has a better way.

Create two formulae. I've called them

f_false_count
if {IsBid} = false then 1 else 0

and f_false_value
if {IsBid} = false then {Vol} else 0

and put them in the detail section.

In the report footer create a sum of both of these formulae. These will give you the number and total value of the false records.

Create another formula. I've called it f_mean :
Sum ({@f_false_value})/Sum ({@f_false_count})

This will give you the average.

BTW it helps if you mention which version you're using and as much supporting information as possible.

HTH,

E11a