Difficult if...then statement problem 1

[travelerII]

I am using Crystal 10 and have created a report that shows the quanity of sales of each product for each month over the last 6 months and the value of the 6 month sales quantity average.

Now the problem I face is I need to look at those last 6 months and if they have a sales quantity greater than 0 in 4 or more of those months and an average value of greater than $9500 those products need to be designated "P-H" if they have a sales quantity greater than 0 in 4 or more of those months and an average value of less than or equal to $9500 those products need to be designated "P" everything else designated "S".

I figured I could accomplish this with an if/then statement however I am not sure how to count how many of the trailing 6 month sales quantity columns have amounts greater that 0.
I tried the following but it wouldn't work

if COUNT(Sum ({@Cur Mo -1}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -2}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -3}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -4}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -5}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -6}, {ITMMASTER.ITMREF_0}))">0")>=4
and {@6 Mo Avg Value} > 9500.00
then "P-H"
else
if COUNT(Sum ({@Cur Mo -1}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -2}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -3}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -4}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -5}, {ITMMASTER.ITMREF_0}),Sum ({@Cur Mo -6}, {ITMMASTER.ITMREF_0}))">0")>=4
and {@6 Mo Avg Value} <= 9500.00
then "P"
else "S"

Any thoughts on how to approach this problem?
Thanks

 

[lbass]

I think you need to replace each sum with a formula like this:

//{@Cur-Mo1>0}
if Sum ({@Cur Mo -1}, {ITMMASTER.ITMREF_0}) > 0 then 1

Repeat for each sum.

Then create a formula like this following (note the square brackets around the array):

if sum([{@Cur-Mo1>0},{@Cur-Mo2>0}, {@Cur-Mo3>0}, etc.]) > 4 and
{@6 Mo Avg Value} > 9500.00 then
"P-H" then //etc.

-LB

 

[travelerII]

Thanks. Exactly what I needed.