3 Replies Latest reply on Jan 14, 2020 3:46 PM by swaroop.gantela

# IF Statement Help!!

Contact ID
Contact Name
Product Line
Carry or Not
Calculation
1Super Store112

2

Null1
332
4Null1
5Null1
662
772
Contact IDContact Name
Calculation
1Super Store2

Hello All,

I'm trying to write a if statement to add points for contacts.

- First table shows to data i'm working with (Carry or Not displays the Product Line # if it's been sold from contact).

- Second table shows the result i'm getting.

- In my Calculated Field (Below) Super Store is showing to have 2 points even though it didn't meet the requirements of at least Product 1,5, and 6.

I'm not sure where exactly i'm going wrong, but if someone can please show me where I went wrong that would be greatly appreciated!

IF COUNT([Carry or Not]) = 7 THEN 3                                                                                                               //3 points if you sold all Product Lines

ELSEIF (([product Line] = '1') = ([Carry or Not] = '1')) AND (([product Line] = '5') = ([Carry or Not] = '5'))

AND (([product Line] = '6') = ([Carry or Not] = '6')) THEN 2                                                                    //To earn 2 points you have to sell from at least Product Line 1,5, and 6

ELSEIF COUNT([Carry or Not]) >= 2 THEN 1                                                                                                //1 point if you sold at least 2 Product Lines

ELSE 0                                                                                                                                                            //0 point if didn't sell any

END

• ###### 1. Re: IF Statement Help!!

Jun,

I think the issue is the mixing the count across the whole Contact,

and the checking of individual product lines.

For me, it is easier to see with Level of Detail calculations:

//3 points if you sold all Product Lines
IF { FIXED [Contact ID],[Contact Name]:COUNTD([Carry or Not])} = 7 THEN 3

//To earn 2 points you have to sell from at least Product Line 1,5, and 6

// flag any row of 1/5/6 if carry=1 and then sum them up to see if it's 3

ELSEIF { FIXED [Contact ID],[Contact Name]:SUM(
IF ([Product Line]='1' AND  [Carry or Not]='1')
OR ([Product Line]='5' AND  [Carry or Not]='5')
OR ([Product Line]='6' AND  [Carry or Not]='6')
THEN 1 ELSE 0 END)}=3 THEN 2

//1 point if you sold at least 2 Product Lines
ELSEIF { FIXED [Contact ID],[Contact Name]:COUNTD([Carry or Not])} >= 2 THEN 1

//0 point if didn't sell any
ELSE 0

END

1 of 1 people found this helpful
• ###### 2. Re: IF Statement Help!!

Thank you very much!!

I've never thought about summing and counting like that, thank you for teaching me something new

• ###### 3. Re: IF Statement Help!!

Jun,