Sum of a Field with Condition

[sikar]

In my report i have a flag field which will have two values 'Y' or 'N'.On the value of this flag i have to sum a field separately for 'Y' and 'N'.After this i have to take difference of these two values.How to do this.Please help me out.

thanks

 

[herbsza]

create the following two fomulas and place in your detail section

Formula @yes

if {flag} = 'Y' then
1
else
0

Formula @No

if {flag} = 'N' then
1
else
0

Then sum the two fomulas

Create a new formula

Formula difference

sum(@yes) - sum(@no)

Place either in a group footer or report footer

I hope this helps

 

[dgillz]

Hersza's solution will help you to COUNT the numbers of Ys and Ns. If you actually want to SUM another field based on the Ys and Ns, slightly modify his formulaa:

{@Yes}
if {flag}="Y" then {AmountField} else 0

{@No}
if {flag}="N" then {AmountField} else 0

Then perform a simple sum on these 2 formulas, and take the difference between them as well in another formula.




Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Madawc]

For Crystal 8.5, I'd use running totals. You can use a formula to decide when to add or whatever, so do one that tests for N and another for Y. More flexible than other methods.

Madawc Williams
East Anglia, Great Britain