6 Replies Latest reply on Jun 30, 2015 11:46 AM by Michel Caissie

# logical condition for calculated field

Hi guys,

I need to replicate exactly condition from it

Filtered By:(1 AND 2 AND 5) OR (3 AND 4)

1. Type equals Renewal,Upsell

2. Stage not equal to Prospect

5. Product not equal to Plus,Premium

3. Stage not equal to Close - lost,Closed - no decision,Prospect

4. Type equals Upsell

My expression looks like it

SUM(IF(([Deal Type]='renewal' or [Deal Type]='upsell') and ([Deal Stage]<>'Prospect') and ([Product]<>'Plus' or [Product]<>'Premium' ))

OR (([Deal Stage]='Close - lost' or [Deal Stage]='Closed - no decision') and [Deal Type]='upsell')

THEN [Amount] END)

I need to calculate "Amount" when condition  match to (1 and 2 and 5) or (3 and 4).

Should I use every time "IF" for new condition?

Thanks,

• ###### 1. Re: logical condition for calculated field

You can all put it in a single IF condition.

I think  you have some parenthesis errors , a missing condition, and some  equal instead of not equal.

Without testing here is what i have. note:  !=  is equivalent to <>   for not equal

1 of 1 people found this helpful
• ###### 2. Re: logical condition for calculated field

Thank you.

• ###### 3. Re: logical condition for calculated field

Something wrong.

I found a case where I have answers renewal, Premium, Prospect

when I used like this

SUM(

IF

(

[Deal Type]='renewal'

and [Deal Stage]<>'Prospect'

)

THEN [Amount] END)

it works, but if

SUM(

IF

(

([Deal Type]='renewal' or [Deal Type]='upsell')

and ([Product]<>'Plus' or [Product]<>'Premium' )

and [Deal Stage]<>'Prospect'

)

THEN [Amount] END)

it counted Product =Premium

• ###### 4. Re: logical condition for calculated field

and ([Product]<>'Plus' or [Product]<>'Premium' )

probably this should be

and ([Product]<>'Plus' and [Product]<>'Premium' )

If you want the Product to be different than 'Plus' and different than 'Premium'

• ###### 5. Re: logical condition for calculated field

I felt that I can make some mistakes

• ###### 6. Re: logical condition for calculated field

I realize that there was a bunch of  or's  instead of  and's  in the main logic. This should be better. 